import openpyxl
import datetime
import time
import os
import sys
import logging
import ChengBanBuMen
import ReplaceBuMen
import configparser
import GenerateCharts
from hanchlogger import logger
import hanchxl
import docx
from docx.shared import RGBColor

timeCur = datetime.datetime.now()
timeCurStr = '{}-{}-{} {}:{}:{}'.format(timeCur.year, timeCur.month, timeCur.day, timeCur.hour, timeCur.minute, timeCur.second)
# dirPath = os.path.dirname(os.path.realpath(sys.executable))
# dirPath ='\\\\172.16.10.175\\共享文件\\数据组\\2023年文件\\杨书记调度会\\生成每日必办'
dirPath ='E:\\日常工作\\数据组日常工作-韩冲\\生成每日必办'
dirPathHuiZong = '\\\\172.16.10.175\\共享文件\\数据组\\月周日报数据'

logFile = dirPath + '\\MustDoEveryDay.log'
logger.get_log(logFile, 'LoggerMustDoEveryDay', logging.DEBUG)

iniFilePathCur = dirPath + '\\MustDoEveryDay.ini'
iniConfigMust = configparser.ConfigParser(allow_no_value=True)
iniConfigMust.read(iniFilePathCur, encoding='utf-8')

timeYesterdayStr = datetime.datetime.fromtimestamp(timeCur.timestamp()-60*60*24).strftime('%Y-%m-%d')
timeYesterdayStr = timeYesterdayStr + '  {}:{}:{}'.format(timeCur.hour, timeCur.minute, timeCur.second)
datatimeYesterday = datetime.datetime.strptime(timeYesterdayStr, '%Y-%m-%d %H:%M:%S')
timeYesterday = time.strptime(timeYesterdayStr, '%Y-%m-%d %H:%M:%S')
timeYesterdayStamp = int(time.mktime(timeYesterday))
logger.debug('昨天（日期）：{}    昨天（时间戳）：{}'.format(timeYesterdayStr, timeYesterdayStamp))

timeTomorrowStr = datetime.datetime.fromtimestamp(timeCur.timestamp()+60*60*24).strftime('%Y-%m-%d')
timeTomorrowStr = timeTomorrowStr + '  {}:{}:{}'.format(timeCur.hour, timeCur.minute, timeCur.second)
datatimeTomorrow = datetime.datetime.strptime(timeTomorrowStr, '%Y-%m-%d %H:%M:%S')
timeTomorrow = time.strptime(timeTomorrowStr, '%Y-%m-%d %H:%M:%S')
timeTomorrowStamp = int(time.mktime(timeTomorrow))
strTomorrow = '{}年{}月{}日'.format(datatimeTomorrow.year, datatimeTomorrow.month, datatimeTomorrow.day)
logger.debug('明天（日期）：{}    明天（时间戳）：{}'.format(timeTomorrowStr, timeTomorrowStamp))


def get_config_value(ini_config, section, section_key, value_default):
    sectionValue = value_default
    if ini_config.has_section(section):
        if ini_config.has_option(section, section_key):
            sectionValue = ini_config.get(section, section_key, )
            if sectionValue is not None and sectionValue != '':
                sectionValue = '{}'.format(sectionValue)
    return sectionValue


# 是否自定义工作目录
customizeWorkFolder = get_config_value(iniConfigMust, 'global', 'isCustomizeWorkFolder', '0')
if customizeWorkFolder != '0':
    workFolder = get_config_value(iniConfigMust, 'global', 'workFolder', dirPath)
    if workFolder != '' and dirPath != workFolder:
        if os.path.exists(workFolder):
            dirPath = workFolder
            logger.debug('是否自定义工作目录为：{}'.format(dirPath))
        else:
            logger.debug('自定义的工作目录不存在，工作目录：{}'.format(dirPath))
    else:
        logger.debug('工作目录未填写或者为空，或者与默认值相同，使用默认值：{}'.format(dirPath))
else:
    logger.debug('不使用自定义的工作目录，使用默认值:{}'.format(dirPath))

logger.debug('是否处理今日派单工单列表')
isProcessReceiving = get_config_value(iniConfigMust, 'global', 'isProcessReceiving', '1')
if isProcessReceiving == '0':
    isProcessReceiving = False
    logger.debug('不处理今日派单工单列表，isProcessUnresolved = False')
else:
    isProcessReceiving = True
    logger.debug('处理今日派单工单列表，isProcessUnresolved = True')

# 是否处理未处理工单列表
isProcessUnresolved = get_config_value(iniConfigMust, 'global', 'isProcessUnresolved', '1')
if isProcessUnresolved == '0':
    isProcessUnresolved = False
    logger.debug('不处理未处理工单列表，isProcessUnresolved = False')
else:
    isProcessUnresolved = True
    logger.debug('处理未处理工单列表，isProcessUnresolved = True')

fileChengBan = dirPath + '\\承办部门.xlsx'
fileJieDanCount = dirPath + '\\每日必办-接单总量.xlsx'
fileKaoHeCount = dirPath + '\\每日必办-考核总量.xlsx'
fileSanLvCount = dirPath + '\\每日必办-首页三率统计表.xlsx'
fileSanLvSave = dirPath + '\\[内部] 附件1 {}月{}日街乡镇接诉情况统计表.xlsx'.format(timeCur.month, timeCur.day)
fileMustDoSave = dirPath + '\\[内部] 每日必办 {}.{}.docx'.format(timeCur.month, timeCur.day)
fileJieDanToday = dirPath + '\\每日必办-今日接单量-区.xlsx'
fileJieDanTodaySave = dirPath + '\\每日必办-今日接单量-区-匹配承办部门.xlsx'
fileJieDanTodayJieDao = dirPath + '\\每日必办-今日接单量.xlsx'
fileRiHuiZong = dirPathHuiZong + '\\日报汇总数据信息.xlsx'
fileImageTuOneSave = dirPath + '\\图1. 七日诉求量情况.png'
fileMustDoMuBan = dirPath + '\\模板-每日必办.docx'
fileSanLvMuBan = dirPath + '\\模板-街乡镇接诉情况统计表.xlsx'

fileHuiFangCount = dirPath + '\\每日必办-回访工单明细.xlsx'
fileHuiFangQuanBu = dirPath + '\\全部工单.xlsx'
fileHuiFangNo = dirPath + '\\每日必办-回访工单明细-未处理.xlsx'
fileHuiFangChengBan = dirPath + '\\每日必办-回访工单明细-匹配承办部门.xlsx'
fileResultKaoHe = dirPath + '\\每日必办-回访工单明细-处理结果.xlsx'
fileResultSave = dirPath + '\\[内部] 附件2 {}月{}日每日必办工单回访情况统计表.xlsx'.format(timeCur.month, timeCur.day)
fileResultMuBan = dirPath + '\\模板-已办结工单回访情况统计表.xlsx'

fileChengBanReplace = dirPath + '\\需替换部门.xlsx'

chengBan = ChengBanBuMen.ChengBanDirectory()
chengBan.load_cheng_ban_list(fileChengBan)
listChengBanKeShi = chengBan.get_list_ke_shi_array()
listChengBanSheQu = chengBan.get_list_she_qu_array()

# 打开替换部门字典表
logger.debug('打开替换部门字典表 文件：{}'.format(fileChengBanReplace))
replaceBuMen = ReplaceBuMen.ReplaceDirectory()
replaceBuMen.load_directory(fileChengBanReplace)
listReplaceBuMen = replaceBuMen.get_replace_directory()

# 打开 模板-每日必办 文件
docMuBan = docx.Document(fileMustDoMuBan)


class GongDanCountInFo(object):
    def __init__(self, time_d=datetime.datetime.now()):
        self.time_d = time_d
        self.date = '{}-{}-{}'.format(time_d.year, time_d.month, time_d.day)
        self.jieDanCount = 0
        self.jieDanShiPai = 0
        self.jieDanQuPai = 0
        self.kaoHeCount = 0
        self.kaoHeShiPai = 0
        self.kaoHeQuPai = 0
        self.lianXi = 0
        self.weiLanXi = 0
        self.jieJue = 0
        self.weiJieJue = 0
        self.manYi = 0
        self.jiBenManYi = 0
        self.buManYi = 0
        self.jianMian = 0
        self.weiJianMian = 0
        self.lvLianXi = 0
        self.lvJieJue = 0
        self.lvManYi = 0
        self.lvJianMian = 0
        self.deFen = 0
        self.muBiao = 0
        self.quPaiMing = 0
        self.xiaoZuPaiMing = 0


class jieDanInFo(object):
    def __init__(self, time_d=datetime.datetime.now()):
        self.date = '{}-{}-{}'.format(time_d.year, time_d.month, time_d.day)
        self.jieDanCount = 0
        self.jieDanShiPai = 0
        self.jieDanQuPai = 0
        self.jieDanKeShi = 0
        self.jieDanSheQu = 0
        self.jieDanYesterday = 0
        # 分类统计（二级分类）
        self.countFenLei = {}
        # 承办部门-科室
        self.chengBanKeShi = {}
        # 承办部门-社区村
        self.chengBanSheQu = {}


class dateJieDanInFo(object):
    def __init__(self):
        self.date = ''
        self.count = 0
        self.shiPai = 0
        self.quPai = 0


# 打开 本考期案件明细表-总量.xlsx
bookJieDan = openpyxl.load_workbook(fileJieDanCount, data_only=True)
sheetJieDan = bookJieDan.active
maxRowJieDan = sheetJieDan.max_row
maxColJieDan = sheetJieDan.max_column
logger.debug('本考期案件明细表-总量.xlsx -------行数：{}行    列数：{}列'.format(maxRowJieDan, maxColJieDan))

countInfo = GongDanCountInFo()
colLeiXing = hanchxl.get_col_index(sheetJieDan, '派单类型')
for row in range(2, maxRowJieDan + 1):
    countInfo.jieDanCount = countInfo.jieDanCount + 1
    valueLeiXing = '{}'.format(sheetJieDan.cell(row, colLeiXing).value).strip()
    if valueLeiXing == '直派' or valueLeiXing == '市派':
        countInfo.jieDanShiPai = countInfo.jieDanShiPai + 1
    elif valueLeiXing == '转派' or valueLeiXing == '区派':
        countInfo.jieDanQuPai = countInfo.jieDanQuPai + 1

# 打开 日报汇总数据信息.xlsx
bookRiHuiZong = openpyxl.load_workbook(fileRiHuiZong, data_only=True)
sheetCountJieDan = bookRiHuiZong['总体情况']
maxRowCountJieDan = sheetCountJieDan.max_row
maxColCountJieDan = sheetCountJieDan.max_column
logger.debug('{} 总体诉求情况 -------行数：{}行    列数：{}列'.format(fileRiHuiZong, maxRowCountJieDan, maxColCountJieDan))

# 打开 本考期案件明细表-考核.xlsx
bookKaoHe = openpyxl.load_workbook(fileKaoHeCount, data_only=True)
sheetKaoHe = bookKaoHe.active
maxRowKaoHe = sheetKaoHe.max_row
maxColKaoHe = sheetKaoHe.max_column
logger.debug('本考期案件明细表-考核.xlsx -------行数：{}行    列数：{}列'.format(maxRowKaoHe, maxColKaoHe))

colLianXi = hanchxl.get_col_index(sheetKaoHe, '是否联系')
colJieJue = hanchxl.get_col_index(sheetKaoHe, '是否解决')
colManYi = hanchxl.get_col_index(sheetKaoHe, '是否满意')
colJianMian = hanchxl.get_col_index(sheetKaoHe, '是否见面')
colLeiXing = hanchxl.get_col_index(sheetKaoHe, '派单类型')
logger.debug('是否联系:{} 是否解决:{} 是否满意:{} 派单类型:{} '.format(colLianXi, colJieJue, colManYi, colLeiXing))
for row in range(2, maxRowKaoHe + 1):
    countInfo.kaoHeCount = countInfo.kaoHeCount + 1
    valueLianXi = '{}'.format(sheetKaoHe.cell(row, colLianXi).value).strip()
    valueJieJue = '{}'.format(sheetKaoHe.cell(row, colJieJue).value).strip()
    valueManYi = '{}'.format(sheetKaoHe.cell(row, colManYi).value).strip()
    valueJianMian = '{}'.format(sheetKaoHe.cell(row, colJianMian).value).strip()
    valueLeiXing = '{}'.format(sheetKaoHe.cell(row, colLeiXing).value).strip()
    if valueLeiXing == '直派' or valueLeiXing == '市派':
        countInfo.kaoHeShiPai = countInfo.kaoHeShiPai + 1
    elif valueLeiXing == '转派' or valueLeiXing == '区派':
        countInfo.kaoHeQuPai = countInfo.kaoHeQuPai + 1
    if valueLianXi == '已联系' or valueLianXi == '联系':
        countInfo.lianXi = countInfo.lianXi + 1
    elif valueLianXi == '未联系':
        countInfo.weiLanXi = countInfo.weiLanXi + 1
    if valueJieJue == '已解决' or valueJieJue == '解决':
        countInfo.jieJue = countInfo.jieJue + 1
    if valueJieJue == '未解决':
        countInfo.weiJieJue = countInfo.weiJieJue + 1
    if valueManYi == '满意' or valueManYi == '非常满意':
        countInfo.manYi = countInfo.manYi + 1
    if valueManYi == '基本满意':
        countInfo.jiBenManYi = countInfo.jiBenManYi + 1
    if valueManYi == '不满意' or valueManYi == '非常不满意':
        countInfo.buManYi = countInfo.buManYi + 1
    if valueJieJue == '已见面' or valueJianMian == '见面':
        countInfo.jianMian = countInfo.jianMian + 1
    if valueJianMian == '未见面':
        countInfo.weiJianMian = countInfo.weiJianMian + 1

logger.debug('打开首页三率统计表.xlsx')
# 打开 首页三率统计表.xlsx
bookSanLv = openpyxl.load_workbook(fileSanLvCount, data_only=True)
sheetSanLv = bookSanLv.active
maxRowSanLv = sheetSanLv.max_row
maxColSanLv = sheetSanLv.max_column
logger.debug('首页三率统计表.xlsx -------行数：{}行    列数：{}列'.format(maxRowSanLv, maxColSanLv))

# 打开 模板-街乡镇接诉情况统计表.xlsx
bookSanLvMuBan = openpyxl.load_workbook(fileSanLvMuBan, data_only=True)
sheetSanLvMuBan = bookSanLvMuBan.active
maxRowSanLvMuBan = sheetSanLvMuBan.max_row
maxColSanLvMuBan = sheetSanLvMuBan.max_column
logger.debug('模板-街乡镇接诉情况统计表.xlsx -------行数：{}行    列数：{}列'.format(maxRowSanLvMuBan, maxColSanLvMuBan))

strHuiFangStart = '2023年{}月{}日9时'.format(datatimeYesterday.month, datatimeYesterday.day)
strKaoHeStart = ''
strTitle = ''
if timeCur.day < 19:
    strKaoHeStart = '2023年{}月19日0时'.format(timeCur.month - 1)
    strTitle = strTitle + '{}月19日-{}月{}日'.format(timeCur.month - 1, timeCur.month, timeCur.day)
else:
    strKaoHeStart = '2023年{}月19日0时'.format(timeCur.month)
    strTitle = strTitle + '{}月19日-{}月{}日'.format(timeCur.month, timeCur.month, timeCur.day)
strTitle = strTitle + '街乡镇接诉情况统计表\r\n（截至 12:00 裸分排名）'
sheetSanLvMuBan.cell(2, 1).value = strTitle

xiaoZuList = ['拱辰街道', '城关街道', '窦店镇', '西潞街道', '阎村镇', '长阳镇', '琉璃河镇', '青龙湖镇']
saveTitleList = ['工单\n总量', '工单量\n目标值', '区级\n有效回访', '响应率', '解决率', '满意率', '综合\n评分', '差值', '见面率']

colListMuBan = {}
colList = {}
for index in saveTitleList:
    colList[index] = hanchxl.get_col_index(sheetSanLv, index, 2)
    colListMuBan[index] = hanchxl.get_col_index(sheetSanLvMuBan, index, 3)
    # logger.debug('数据{}:{}   保存：{}:{}'.format(index, colList[index], index, colListMuBan[index]))

colXuHao = hanchxl.get_col_index(sheetSanLv, '序号', 2)
colName = hanchxl.get_col_index(sheetSanLv, '名称', 2)

colNameSave = hanchxl.get_col_index(sheetSanLvMuBan, '承办单位', 3)
colPaiMingSave = hanchxl.get_col_index(sheetSanLvMuBan, '裸分\n排名', 3)

rowInsert = 4
for row in range(3, maxRowSanLv + 1):
    valueName = '{}'.format(sheetSanLv.cell(row, colName).value).strip()
    if valueName in xiaoZuList:
        sheetSanLvMuBan.cell(rowInsert, colNameSave).value = valueName
        sheetSanLvMuBan.cell(rowInsert, colPaiMingSave).value = '{}'.format(int(sheetSanLv.cell(row, colXuHao).value))
        for index in saveTitleList:
            sheetSanLvMuBan.cell(rowInsert, colListMuBan[index]).value = sheetSanLv.cell(row, colList[index]).value
        if valueName == '拱辰街道':
            valueLianXi = '{}'.format(sheetSanLv.cell(row, colList['响应率']).value).strip('%')
            if valueLianXi != '-':
                countInfo.lvLianXi = round(float(valueLianXi), 0)
            else:
                countInfo.lvLianXi = '-'
            valueJieJue = '{}'.format(sheetSanLv.cell(row, colList['解决率']).value).strip('%')
            if valueJieJue != '-':
                countInfo.lvJieJue = round(float(valueJieJue), 0)
            else:
                countInfo.lvJieJue = '-'
            valueManYi = '{}'.format(sheetSanLv.cell(row, colList['满意率']).value).strip('%')
            if valueManYi != '-':
                countInfo.lvManYi = round(float(valueManYi), 0)
            else:
                countInfo.lvManYi = '-'
            valueJianMain = '{}'.format(sheetSanLv.cell(row, colList['见面率']).value).strip('%')
            if valueJianMain != '-':
                countInfo.lvJianMian = round(float(valueJianMain), 0)
            else:
                countInfo.lvJianMian = '-'
            valueDeFen = '{}'.format(sheetSanLv.cell(row, colList['综合\n评分']).value).strip('%')
            if valueDeFen != '-':
                countInfo.deFen = round(float(valueDeFen), 0)
            else:
                countInfo.deFen = '-'
            countInfo.quPaiMing = int(sheetSanLv.cell(row, colXuHao).value)
            countInfo.xiaoZuPaiMing = rowInsert - 3
        rowInsert = rowInsert + 1

bookSanLvMuBan.save(fileSanLvSave)


# 保存考核工单信息
colTitleListArry = ['时间戳', '日期', '接单总量', '接单区派', '接单市派', '考核总量', '考核市派', '考核区派', '联系', '未联系',
                    '解决', '未解决', '满意', '基本满意', '不满意', '见面', '未见面','联系率', '解决率', '满意率', '见面率',
                    '综合得分', '区排名', '小组排名']
colTitleList = {}
for index in colTitleListArry:
    colTitleList[index] = hanchxl.get_col_index(sheetCountJieDan, index)
    # logger.debug('{}:{}'.format(index, colTitleList[index]))

# 保存接单总量信息
for index in colTitleListArry:
    logger.debug('{}:{}'.format(index, colTitleList[index]))
    if index == '时间戳':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = timeCurStr
    elif index == '日期':
        todayStr = '{}-{}-{}'.format(timeCur.year, timeCur.month, timeCur.day)
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = todayStr
    elif index == '接单总量':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.jieDanCount
    elif index == '接单市派':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.jieDanShiPai
    elif index == '接单区派':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.jieDanQuPai
    elif index == '考核总量':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.kaoHeCount
    elif index == '考核市派':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.kaoHeShiPai
    elif index == '考核区派':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.kaoHeQuPai
    elif index == '联系':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.lianXi
    elif index == '未联系':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.weiLanXi
    elif index == '解决':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.jieJue
    elif index == '未解决':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.weiJieJue
    elif index == '满意':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.manYi
    elif index == '基本满意':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.jiBenManYi
    elif index == '不满意':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.buManYi
    elif index == '见面':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.jianMian
    elif index == '未见面':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.weiJianMian
    elif index == '联系率':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.lvLianXi
    elif index == '解决率':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.lvJieJue
    elif index == '满意率':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.lvManYi
    elif index == '见面率':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.lvJianMian
    elif index == '综合得分':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.deFen
    elif index == '区排名':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.quPaiMing
    elif index == '小组排名':
        sheetCountJieDan.cell(maxRowCountJieDan + 1, colTitleList[index]).value = countInfo.xiaoZuPaiMing


# 打开 今日接单量-区.xlsx
bookJieDanToday = openpyxl.load_workbook(fileJieDanToday, data_only=True)
sheetJieDanToday = bookJieDanToday.active
maxRowJieDanToday = sheetJieDanToday.max_row
maxColJieDanToday = sheetJieDanToday.max_column
logger.debug('今日接单量-区.xlsx -------行数：{}行    列数：{}列'.format(maxRowJieDanToday, maxColJieDanToday))

# 打开 今日接单量.xlsx
bookJieDanTodayJieDao = openpyxl.load_workbook(fileJieDanTodayJieDao, data_only=True)
sheetJieDanTodayJieDao = bookJieDanTodayJieDao.active
maxRowJieDanTodayJieDao = sheetJieDanTodayJieDao.max_row
maxColJieDanTodayJieDao = sheetJieDanTodayJieDao.max_column
logger.debug('今日接单量.xlsx -------行数：{}行    列数：{}列'.format(maxRowJieDanTodayJieDao, maxColJieDanTodayJieDao))


indexCanYuQu = hanchxl.get_col_index(sheetJieDanToday, '参与部门')
sheetJieDanToday.cell(1, indexCanYuQu).value = '参与部门（区）'

logger.debug('插入 承办部门')
colGongDanIndex = hanchxl.get_col_index(sheetJieDanToday, '工单编号')
sheetJieDanToday.insert_cols(colGongDanIndex + 1, 2)
sheetJieDanToday.cell(1, colGongDanIndex + 1).value = '承办部门'
sheetJieDanToday.cell(1, colGongDanIndex + 2).value = '参与部门'

# 匹配区系统 承办部门 和 参与部门
hanchxl.pi_pei_cheng_ban_qu_xi_tong_2(sheetJieDanToday, '主办部门名称', '从办部门名称', '承办部门', '参与部门')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDanToday, '承办部门', '应急组', '应急管理组')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDanToday, '承办部门', '项目组', '项目工作组')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDanToday, '承办部门', '执法队', '综合行政执法队')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDanToday, '承办部门', '执法队一组', '综合城管一组')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDanToday, '承办部门', '执法队二组', '综合城管二组')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDanToday, '承办部门', '执法队三组', '综合城管三组')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDanToday, '承办部门', '执法队四组', '综合城管四组')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDanToday, '承办部门', '执法队五组', '综合城管五组')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDanToday, '承办部门', '拱辰教委', '教委')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDanToday, '承办部门', '尚林家园社区', '尚林家园党支部')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDanToday, '承办部门', '荟悦嘉苑社区', '荟悦嘉苑党支部')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDanToday, '承办部门', '市政环境组', '环境管理组')
#
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDanToday, '参与部门', '应急组', '应急管理组')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDanToday, '参与部门', '项目组', '项目工作组')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDanToday, '参与部门', '执法队', '综合行政执法队')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDanToday, '参与部门', '执法队一组', '综合城管一组')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDanToday, '参与部门', '执法队二组', '综合城管二组')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDanToday, '参与部门', '执法队三组', '综合城管三组')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDanToday, '参与部门', '执法队四组', '综合城管四组')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDanToday, '参与部门', '执法队五组', '综合城管五组')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDanToday, '参与部门', '拱辰教委', '教委')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDanToday, '参与部门', '尚林家园社区', '尚林家园党支部')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDanToday, '参与部门', '荟悦嘉苑社区', '荟悦嘉苑党支部')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDanToday, '参与部门', '市政环境组', '环境管理组')

colPaiDanLeiXingIndex = hanchxl.get_col_index(sheetJieDanToday, '派单类型')
sheetJieDanToday.cell(1, colPaiDanLeiXingIndex).value = '工单类型(直派/转派)'
# 根据 ‘工单编号’ 匹配 ‘承办部门’ 和 ‘参与部门’
hanchxl.pi_pei_te_ding_col(sheetJieDanToday, '工单编号', sheetJieDanTodayJieDao, '承办部门', '')
# 根据 ‘工单编号’ 匹配 ‘承办部门’ 和 ‘参与部门’
hanchxl.pi_pei_te_ding_col(sheetJieDanToday, '工单编号', sheetJieDanTodayJieDao, '所属辖区', '')

for buMenInfo in listReplaceBuMen:
    if buMenInfo.isAll is True:
        hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDanToday, '承办部门', buMenInfo.nameYuan, buMenInfo.nameNow)
        hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDanToday, '参与部门', buMenInfo.nameYuan, buMenInfo.nameNow)
    else:
        hanchxl.xiu_gai_cheng_ban_bu_men(sheetJieDanToday, '承办部门', buMenInfo.nameYuan, buMenInfo.nameNow)
        hanchxl.xiu_gai_cheng_ban_bu_men(sheetJieDanToday, '参与部门', buMenInfo.nameYuan, buMenInfo.nameNow)

# # 修改承办部门
# hanchxl.xiu_gai_cheng_ban_bu_men(sheetJieDanToday, '承办部门', '应急小分队', '市民诉求处置中心')
# hanchxl.xiu_gai_cheng_ban_bu_men(sheetJieDanToday, '承办部门', '疑难工单', '市民诉求处置中心')
# hanchxl.xiu_gai_cheng_ban_bu_men(sheetJieDanToday, '承办部门', '疑难案件', '市民诉求处置中心')
# hanchxl.xiu_gai_cheng_ban_bu_men(sheetJieDanToday, '承办部门', '流动人口管理组', '社会治理组')
# hanchxl.xiu_gai_cheng_ban_bu_men(sheetJieDanToday, '承办部门', '综合城管', '综合行政执法队')

bookJieDanToday.save(fileJieDanTodaySave)

jieDanTodayInfo = jieDanInFo()
indexCode = hanchxl.get_col_index(sheetJieDanToday, '工单编号')
indexLeixing = hanchxl.get_col_index(sheetJieDanToday, '工单类型(直派/转派)')
indexFenLei = hanchxl.get_col_index(sheetJieDanToday, '二级分类')
indexChengBan = hanchxl.get_col_index(sheetJieDanToday, '承办部门')

if indexLeixing is not None and indexFenLei is not None and indexChengBan is not None :
    for rowSource in range(2, maxRowJieDanToday + 1):
        cellValueLeiXing = '{}'.format(sheetJieDanToday.cell(rowSource, indexLeixing).value).strip()
        cellValueFenLei = '{}'.format(sheetJieDanToday.cell(rowSource, indexFenLei).value).strip()
        cellValueChengBan = '{}'.format(sheetJieDanToday.cell(rowSource, indexChengBan).value).strip()
        if cellValueLeiXing == '' or cellValueLeiXing.lower() == 'none':
            continue
        jieDanTodayInfo.jieDanCount = jieDanTodayInfo.jieDanCount + 1
        # 判断 工单类型(直派/转派)
        if cellValueLeiXing == '直派' or cellValueLeiXing == '市派':
            jieDanTodayInfo.jieDanShiPai = jieDanTodayInfo.jieDanShiPai + 1
        elif cellValueLeiXing == '已分派' or cellValueLeiXing == '区派':
            jieDanTodayInfo.jieDanQuPai = jieDanTodayInfo.jieDanQuPai + 1
        # 判断工单分类
        if cellValueFenLei == '':
            cellValueFenLei = '其他问题'
        if cellValueFenLei in jieDanTodayInfo.countFenLei:
            jieDanTodayInfo.countFenLei[cellValueFenLei] = jieDanTodayInfo.countFenLei[cellValueFenLei] + 1
        else:
            jieDanTodayInfo.countFenLei[cellValueFenLei] = 1
        # 判断承办部门
        if cellValueChengBan in listChengBanSheQu:
            jieDanTodayInfo.jieDanSheQu = jieDanTodayInfo.jieDanSheQu + 1
            if cellValueChengBan in jieDanTodayInfo.chengBanSheQu:
                jieDanTodayInfo.chengBanSheQu[cellValueChengBan] = jieDanTodayInfo.chengBanSheQu[cellValueChengBan] + 1
            else:
                jieDanTodayInfo.chengBanSheQu[cellValueChengBan] = 1
        elif cellValueChengBan in listChengBanKeShi:
            jieDanTodayInfo.jieDanKeShi = jieDanTodayInfo.jieDanKeShi + 1
            if cellValueChengBan in jieDanTodayInfo.chengBanKeShi:
                jieDanTodayInfo.chengBanKeShi[cellValueChengBan] = jieDanTodayInfo.chengBanKeShi[cellValueChengBan] + 1
            else:
                jieDanTodayInfo.chengBanKeShi[cellValueChengBan] = 1


logger.debug('科室承办：{}'.format(jieDanTodayInfo.chengBanKeShi))
logger.debug('社区（村）承办：{}'.format(jieDanTodayInfo.chengBanSheQu))

sheetCountRiSuQiu = bookRiHuiZong['日诉求量']
maxRowCountRiSuQiu = sheetCountRiSuQiu.max_row
maxColCountRiSuQiu = sheetCountRiSuQiu.max_column
logger.debug('{} 日诉求量 -------行数：{}行    列数：{}列'.format(fileRiHuiZong, maxRowCountRiSuQiu, maxColCountRiSuQiu))

dateYesterdayString = '{}月{}日'.format(datatimeYesterday.month, datatimeYesterday.day)

colTimestamp = hanchxl.get_col_index(sheetCountRiSuQiu, '时间戳')
colDate = hanchxl.get_col_index(sheetCountRiSuQiu, '日期')
colRiSuQiu = hanchxl.get_col_index(sheetCountRiSuQiu, '日诉求量')
colRiQuPai = hanchxl.get_col_index(sheetCountRiSuQiu, '区派')
colRiShiPai = hanchxl.get_col_index(sheetCountRiSuQiu, '市派')
colRiKeShi = hanchxl.get_col_index(sheetCountRiSuQiu, '科室承办')
colRiSheQu = hanchxl.get_col_index(sheetCountRiSuQiu, '社区（村）承办')
if colDate is not None:
    for row in range(2, maxRowCountRiSuQiu + 1):
        valueDate = '{}'.format(sheetCountRiSuQiu.cell(row, colDate).value).strip()
        logger.debug('{}  {}'.format(valueDate, dateYesterdayString))
        if valueDate == dateYesterdayString:
            jieDanTodayInfo.jieDanYesterday = sheetCountRiSuQiu.cell(row, colRiSuQiu).value

sheetCountRiSuQiu.cell(maxRowCountRiSuQiu + 1, colTimestamp).value = timeCurStr
sheetCountRiSuQiu.cell(maxRowCountRiSuQiu + 1, colDate).value = '{}月{}日'.format(timeCur.month, timeCur.day)
sheetCountRiSuQiu.cell(maxRowCountRiSuQiu + 1, colRiSuQiu).value = jieDanTodayInfo.jieDanCount
sheetCountRiSuQiu.cell(maxRowCountRiSuQiu + 1, colRiQuPai).value = jieDanTodayInfo.jieDanQuPai
sheetCountRiSuQiu.cell(maxRowCountRiSuQiu + 1, colRiShiPai).value = jieDanTodayInfo.jieDanShiPai
sheetCountRiSuQiu.cell(maxRowCountRiSuQiu + 1, colRiKeShi).value = jieDanTodayInfo.jieDanKeShi
sheetCountRiSuQiu.cell(maxRowCountRiSuQiu + 1, colRiSheQu).value = jieDanTodayInfo.jieDanSheQu

# 保存 诉求分类情况 二级分类 开始
countFenLei = jieDanTodayInfo.countFenLei
countFenLei = dict(sorted(countFenLei.items(), key=lambda kv: kv[1], reverse=True))
sheetCountFenLei = bookRiHuiZong['分类统计']
maxRow = sheetCountFenLei.max_row
colTimestamp = hanchxl.get_col_index(sheetCountFenLei, '时间戳')
colDate = hanchxl.get_col_index(sheetCountFenLei, '日期')
colRiSuQiu = hanchxl.get_col_index(sheetCountRiSuQiu, '日诉求量')
sheetCountFenLei.cell(maxRow + 1, colTimestamp).value = timeCurStr
sheetCountFenLei.cell(maxRow + 1, colDate).value = jieDanTodayInfo.date
sheetCountFenLei.cell(maxRow + 1, colRiSuQiu).value = jieDanTodayInfo.jieDanCount
FenLeiCount = []
FenLeiCountToday = []
maxCount = 0
countLast = 0
countMaxFenLei = 0
countMaxFenLeiStr = ['一', '二', '三', '四', '五', '六', '七', '八', '九', '十']
for index in countFenLei:
    maxCol = sheetCountFenLei.max_column
    indexLeiXingSearch = hanchxl.get_col_index(sheetCountFenLei, index)
    if indexLeiXingSearch is None:
        indexLeiXingSearch = maxCol + 1
        sheetCountFenLei.cell(1, indexLeiXingSearch).value = index
    sheetCountFenLei.cell(maxRow + 1, indexLeiXingSearch).value = countFenLei[index]
    if maxCount < 5:
        FenLeiCount.append(index)
        FenLeiCountToday.append(countFenLei[index])
        countLast = countFenLei[index]
        countMaxFenLei = countMaxFenLei + countFenLei[index]
    maxCount = maxCount + 1
    logger.debug('{} 有 {} 件 在{}列  maxCount:{}'.format(index, countFenLei[index], indexLeiXingSearch, maxCount))

logger.debug('{}'.format(FenLeiCount))

countChengBan = {}
for key in jieDanTodayInfo.chengBanKeShi:
    countChengBan[key] = jieDanTodayInfo.chengBanKeShi[key]

for key in jieDanTodayInfo.chengBanSheQu:
    if key in countChengBan:
        countChengBan[key] = countChengBan[key] + jieDanTodayInfo.chengBanSheQu[key]
    else:
        countChengBan[key] = jieDanTodayInfo.chengBanSheQu[key]

logger.debug('承办部门-排序前：{}'.format(countChengBan))
# 保存 承办部门接单量 开始
# countChengBan = dict(list(jieDanTodayInfo.chengBanKeShi.items())+list(jieDanTodayInfo.chengBanSheQu.items()))
countChengBan = dict(sorted(countChengBan.items(), key=lambda kv: kv[1], reverse=True))
logger.debug('承办部门-排序后：{}'.format(countChengBan))
sheetCountChengBan = bookRiHuiZong['承办部门接单量']
maxRow = sheetCountChengBan.max_row
colTimestamp = hanchxl.get_col_index(sheetCountChengBan, '时间戳')
colDate = hanchxl.get_col_index(sheetCountChengBan, '日期')
colJieDanCount = hanchxl.get_col_index(sheetCountChengBan, '接单总量')

sheetCountChengBan.cell(maxRow + 1, colTimestamp).value = timeCurStr
sheetCountChengBan.cell(maxRow + 1, colDate).value = jieDanTodayInfo.date
sheetCountChengBan.cell(maxRow + 1, colRiSuQiu).value = jieDanTodayInfo.jieDanCount
ChengBanCount = []
ChengBanCountToday = []
maxChengBanCount = 0
for index in countChengBan:
    maxCol = sheetCountChengBan.max_column
    indexChengBanSearch = hanchxl.get_col_index(sheetCountChengBan, index)
    if indexChengBanSearch is None:
        indexChengBanSearch = maxCol + 1
        sheetCountChengBan.cell(1, indexChengBanSearch).value = index
    sheetCountChengBan.cell(maxRow + 1, indexChengBanSearch).value = countChengBan[index]
    if countChengBan[index] >= 5:
        if maxChengBanCount < 10:
            ChengBanCount.append(index)
    maxChengBanCount = maxChengBanCount + 1
    logger.debug('{} 有 {} 件 在{}列  maxCount:{}'.format(index, countChengBan[index], indexLeiXingSearch, maxCount))
logger.debug('{}'.format(ChengBanCount))

maxRow = sheetCountChengBan.max_row
ChengBanCountYesterday = {}
colDate = hanchxl.get_col_index(sheetCountChengBan, '日期')
yesterdayStr = '{}-{}-{}'.format(datatimeYesterday.year, datatimeYesterday.month, datatimeYesterday.day)
for row in range(2, maxRow + 1):
    valueDate = '{}'.format(sheetCountChengBan.cell(row, colDate).value).strip()
    logger.debug('{}  {}'.format(valueDate, yesterdayStr))
    if valueDate == yesterdayStr:
        for index in ChengBanCount:
            colChengBan = hanchxl.get_col_index(sheetCountChengBan, index)
            if colChengBan is not None:
                valueChengBan = sheetCountChengBan.cell(row, colChengBan).value
                if valueChengBan is not None:
                    ChengBanCountYesterday[index] = valueChengBan
                else:
                    ChengBanCountYesterday[index] = 0
            else:
                ChengBanCountYesterday[index] = 0
logger.debug('ChengBanCountYesterday:{}'.format(ChengBanCountYesterday))


# 打开 每日必办-回访工单明细.xlsx
bookHuiFang = openpyxl.load_workbook(fileHuiFangCount, data_only=True)
sheetHuiFang = bookHuiFang.active
maxRowHuiFang = sheetHuiFang.max_row
maxColHuiFang = sheetHuiFang.max_column
logger.debug('每日必办-回访工单明细.xlsx -------行数：{}行    列数：{}列'.format(maxRowHuiFang, maxColHuiFang))

# 打开 全部工单.xlsx
bookHuiFangQuanBu = openpyxl.load_workbook(fileHuiFangQuanBu, data_only=True)
sheetHuiFangQuanBu = bookHuiFangQuanBu.active
maxRowHuiFangQuanBu = sheetHuiFangQuanBu.max_row
maxColHuiFangQuanBu = sheetHuiFangQuanBu.max_column
logger.debug('全部工单.xlsx -------行数：{}行    列数：{}列'.format(maxRowHuiFangQuanBu, maxColHuiFangQuanBu))

colCanYuQuIndex = hanchxl.get_col_index(sheetHuiFang, '参与部门')
sheetHuiFang.cell(1, colCanYuQuIndex).value = '参与部门（区）'

colChengBan = hanchxl.get_col_index(sheetHuiFang, '承办单位')
sheetHuiFang.insert_cols(colChengBan, 3)
sheetHuiFang.cell(1, colChengBan).value = '承办部门'
sheetHuiFang.cell(1, colChengBan+1).value = '参与部门'
sheetHuiFang.cell(1, colChengBan+2).value = '是否处理'

# 匹配区系统 承办部门 和 参与部门
hanchxl.pi_pei_cheng_ban_qu_xi_tong_2(sheetHuiFang, '主办部门名称', '从办部门名称', '承办部门', '参与部门')
# 根据 ‘工单编号’ 匹配 ‘承办部门’ 和 ‘参与部门’
hanchxl.pi_pei_te_ding_col(sheetHuiFang, '工单编号', sheetHuiFangQuanBu, '承办部门', '参与部门')
for buMenInfo in listReplaceBuMen:
    if buMenInfo.isAll is True:
        hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetHuiFang, '承办部门', buMenInfo.nameYuan, buMenInfo.nameNow)
        hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetHuiFang, '参与部门', buMenInfo.nameYuan, buMenInfo.nameNow)
    else:
        hanchxl.xiu_gai_cheng_ban_bu_men(sheetHuiFang, '承办部门', buMenInfo.nameYuan, buMenInfo.nameNow)
        hanchxl.xiu_gai_cheng_ban_bu_men(sheetHuiFang, '参与部门', buMenInfo.nameYuan, buMenInfo.nameNow)

# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetHuiFang, '承办部门', '应急组', '应急管理组')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetHuiFang, '承办部门', '项目组', '项目工作组')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetHuiFang, '承办部门', '执法队', '综合行政执法队')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetHuiFang, '承办部门', '执法队一组', '综合城管一组')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetHuiFang, '承办部门', '执法队二组', '综合城管二组')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetHuiFang, '承办部门', '执法队三组', '综合城管三组')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetHuiFang, '承办部门', '执法队四组', '综合城管四组')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetHuiFang, '承办部门', '执法队五组', '综合城管五组')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetHuiFang, '承办部门', '拱辰教委', '教委')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetHuiFang, '承办部门', '尚林家园社区', '尚林家园党支部')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetHuiFang, '承办部门', '荟悦嘉苑社区', '荟悦嘉苑党支部')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetHuiFang, '承办部门', '市政环境组', '环境管理组')

# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetHuiFang, '参与部门', '应急组', '应急管理组')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetHuiFang, '参与部门', '项目组', '项目工作组')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetHuiFang, '参与部门', '执法队', '综合行政执法队')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetHuiFang, '参与部门', '执法队一组', '综合城管一组')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetHuiFang, '参与部门', '执法队二组', '综合城管二组')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetHuiFang, '参与部门', '执法队三组', '综合城管三组')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetHuiFang, '参与部门', '执法队四组', '综合城管四组')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetHuiFang, '参与部门', '执法队五组', '综合城管五组')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetHuiFang, '参与部门', '拱辰教委', '教委')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetHuiFang, '参与部门', '尚林家园社区', '尚林家园党支部')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetHuiFang, '参与部门', '荟悦嘉苑社区', '荟悦嘉苑党支部')
# hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetHuiFang, '参与部门', '市政环境组', '环境管理组')



# hanchxl.xiu_gai_cheng_ban_bu_men(sheetHuiFang, '承办部门', '疑难工单', '市民诉求处置中心')
# hanchxl.xiu_gai_cheng_ban_bu_men(sheetHuiFang, '承办部门', '疑难案件', '市民诉求处置中心')
# hanchxl.xiu_gai_cheng_ban_bu_men(sheetHuiFang, '承办部门', '应急小分队', '市民诉求处置中心')
# hanchxl.xiu_gai_cheng_ban_bu_men(sheetHuiFang, '承办部门', '流动人口管理组', '社会治理组')
# hanchxl.xiu_gai_cheng_ban_bu_men(sheetHuiFang, '承办部门', '智汇雅苑诺亚方舟物业', '智汇雅苑社区')
# hanchxl.xiu_gai_cheng_ban_bu_men(sheetHuiFang, '承办部门', '综合城管', '综合行政执法队')

bookHuiFang.save(fileHuiFangChengBan)

colZhuanKaoHe = 7              # 转派-考核
colZhuanLian = 8              # 转派-联系
colZhuanLianNo = 9            # 转派-未联系
colZhuanJie = 10              # 转派-解决
colZhuanJieNo = 11            # 转派-未解决
colZhuanMan = 12              # 转派-满意
colZhuanJiBen = 13            # 转派-基本满意
colZhuanManNo = 14             # 转派-不满意
colZhuanJian = 15                #转派-见面
colZhuanJianNo = 16              #转派-未见面
colZhuanlvLian = 17              # 转派-响应率
colZhuanLvJie = 18             # 转派-解决率
colZhuanLvMan = 19            # 转派-满意率
colZhuanLvJian = 20              # 转派-见面率

colZhiKaoHe = 21              # 直派-考核
colZhiLian = 22              # 直派-联系
colZhiLianNo = 23            # 直派-未联系
colZhiJie = 24              # 直派-解决
colZhiJieNo = 25            # 直派-未解决
colZhiMan = 26              # 直派-满意
colZhiJiBen = 27            # 直派-基本满意
colZhiManNo = 28             # 直派-不满意
colZhiJian = 29              # 直派-见面
colZhiJianNo = 30              # 直派-未见面
colZhilvLian = 31              # 直派-响应率
colZhiLvJie = 32             # 直派-解决率
colZhiLvMan = 33            # 直派-满意率
colZhilvJian = 34              # 直派-见面率

colAvgLian = 35             # 平均响应率
colAvgJie = 36              # 平均解决率
colAvgMan = 37              # 平均满意率
colAvgJian = 38              # 平均见面率

colDeFen = 39                   # 得分
colPaiming = 40                 # 排名
colDeFenLuoFen = 41             # 裸分得分
colShiFenLuoFen = 42            # 裸分失分
colSuoShuPianQu = 43            # 所属片区


# bookResultMuBan = openpyxl.load_workbook(fileResultMuBan, data_only=True)
bookResultMuBan = openpyxl.load_workbook(fileResultMuBan)
sheetKeShiResult = bookResultMuBan['科室']
sheetSheQuResult = bookResultMuBan['社区、村']

listKeShi = []
listSheQu = []
listKaoHeZhuan = {}
listKaoHeZhi = {}
listKaoHeZhi = {}
listLianXiZhuan = {}
listLianXiZhi = {}
listWeiLianXiZhuan = {}
listWeiLianXiZhi = {}
listJieJueZhuan = {}
listJieJueZhi = {}
listWeiJieJueZhuan = {}
listWeiJieJueZhi = {}
listManYiZhuan = {}
listManYiZhi = {}
listJiBenZhuan = {}
listJiBenZhi = {}
listBuManYiZhuan = {}
listBuManYiZhi = {}
listJianMianZhuan = {}
listJianMianZhi = {}
listWeiJianMianZhuan = {}
listWeiJianMianZhi = {}

lvLianXiZhi = {}
lvWeiLianXiZhi = {}
lvJieJueZhi = {}
lvWeiJieJueZhi = {}
lvManYiZhi = {}
lvJiBenZhi = {}
lvBuManYiZhi = {}
lvManYiFinalZhi = {}
lvJianMianZhi = {}
lvWeiJianMianZhi = {}
lvLianXiZhuan = {}
lvWeiLianXiZhuan = {}
lvJieJueZhuan = {}
lvWeiJieJueZhuan = {}
lvManYiZhuan = {}
lvJiBenZhuan = {}
lvBuManYiZhuan = {}
lvManYiFinalZhuan = {}
lvJianMianZhuan = {}
lvWeiJianMianZhuan = {}


lvLianXiAverage = {}
lvWeiLianXiAverage = {}
lvJieJueAverage = {}
lvWeiJieJueAverage = {}
lvManYiAverage = {}
lvJiBenAverage = {}
lvBuManYiAverage = {}
lvManYiFinalAverage = {}
lvJianMianAverage = {}
lvWeiJianMianAverage = {}

listJieDanZhi = {}
listJieDanZhuan = {}

deFenLuoFen = {}
shiFenLuoFen = {}

# 社区村的所属片区
suoShuPianQu = {}
# 社区村（科室）的负责人
fuZeRen = {}
# 社区村（科室）的主管领导
zhuGuanLingDao = {}

# 保存科室接单量排名
listJieDanKeShi = {}
# 保存社区村接单量排名
listJieDanSheQu = {}
# 保存科室裸分失分排名
listShiFenKeshi = {}
# 保存社区村裸分失分排名
listShiFenSheQu = {}

listShiFenFenLei = {}


logger.debug('考核-转派：{}'.format(listKaoHeZhuan))
logger.debug('考核-直派：{}'.format(listKaoHeZhi))
logger.debug('联系-转派：{}'.format(listLianXiZhuan))
logger.debug('联系-直派：{}'.format(listLianXiZhi))
logger.debug('未联系-转派：{}'.format(listWeiLianXiZhuan))
logger.debug('未联系-直派：{}'.format(listWeiLianXiZhi))
logger.debug('解决-转派：{}'.format(listJieJueZhuan))
logger.debug('解决-直派：{}'.format(listJieJueZhi))
logger.debug('未解决-转派：{}'.format(listWeiJieJueZhuan))
logger.debug('未解决-直派：{}'.format(listWeiJieJueZhi))
logger.debug('满意-转派：{}'.format(listManYiZhuan))
logger.debug('满意-直派：{}'.format(listManYiZhi))
logger.debug('基本满意-转派：{}'.format(listJiBenZhuan))
logger.debug('基本满意-直派：{}'.format(listJiBenZhi))
logger.debug('不满意-转派：{}'.format(listBuManYiZhuan))
logger.debug('不满意-直派：{}'.format(listBuManYiZhi))

for valueCellKeShi in listChengBanKeShi:
    listKeShi.append(valueCellKeShi)
    listKaoHeZhuan[valueCellKeShi] = 0
    listKaoHeZhi[valueCellKeShi] = 0
    listLianXiZhuan[valueCellKeShi] = 0
    listLianXiZhi[valueCellKeShi] = 0
    listJieJueZhuan[valueCellKeShi] = 0
    listJieJueZhi[valueCellKeShi] = 0
    listManYiZhuan[valueCellKeShi] = 0
    listManYiZhi[valueCellKeShi] = 0
    listJiBenZhuan[valueCellKeShi] = 0
    listJiBenZhi[valueCellKeShi] = 0
    listWeiLianXiZhuan[valueCellKeShi] = 0
    listWeiLianXiZhi[valueCellKeShi] = 0
    listWeiJieJueZhuan[valueCellKeShi] = 0
    listWeiJieJueZhi[valueCellKeShi] = 0
    listBuManYiZhuan[valueCellKeShi] = 0
    listBuManYiZhi[valueCellKeShi] = 0
    listJieDanZhi[valueCellKeShi] = 0
    listJieDanZhuan[valueCellKeShi] = 0
    listJianMianZhuan[valueCellKeShi] = 0
    listJianMianZhi[valueCellKeShi] =0
    listWeiJianMianZhuan[valueCellKeShi] = 0
    listWeiJianMianZhi[valueCellKeShi] = 0
    lvLianXiZhi[valueCellKeShi] = None
    lvWeiLianXiZhi[valueCellKeShi] = None
    lvJieJueZhi[valueCellKeShi] = None
    lvWeiJieJueZhi[valueCellKeShi] = None
    lvManYiZhi[valueCellKeShi] = None
    lvJiBenZhi[valueCellKeShi] = None
    lvBuManYiZhi[valueCellKeShi] = None
    lvManYiFinalZhi[valueCellKeShi] = None
    lvManYiFinalZhi[valueCellKeShi] = None
    lvLianXiZhuan[valueCellKeShi] = None
    lvWeiLianXiZhuan[valueCellKeShi] = None
    lvJieJueZhuan[valueCellKeShi] = None
    lvWeiJieJueZhuan[valueCellKeShi] = None
    lvManYiZhuan[valueCellKeShi] = None
    lvJiBenZhuan[valueCellKeShi] = None
    lvBuManYiZhuan[valueCellKeShi] = None
    lvManYiFinalZhuan[valueCellKeShi] = None
    lvLianXiAverage[valueCellKeShi] = None
    lvWeiLianXiAverage[valueCellKeShi] = None
    lvJieJueAverage[valueCellKeShi] = None
    lvWeiJieJueAverage[valueCellKeShi] = None
    lvManYiAverage[valueCellKeShi] = None
    lvJiBenAverage[valueCellKeShi] = None
    lvBuManYiAverage[valueCellKeShi] = None
    lvManYiFinalAverage[valueCellKeShi] = None
    lvJianMianZhuan[valueCellKeShi] =None
    lvWeiJianMianZhuan[valueCellKeShi] = None
    lvJianMianZhi[valueCellKeShi] = None
    lvWeiJianMianZhi[valueCellKeShi] = None
    lvJianMianAverage[valueCellKeShi] = None
    lvWeiJianMianAverage[valueCellKeShi] = None
    deFenLuoFen[valueCellKeShi] = None
    shiFenLuoFen[valueCellKeShi] = None
    # fuZeRen[valueCellKeShi] = listChengBanKeShi[valueCellKeShi].fuZeRen
    # zhuGuanLingDao[valueCellKeShi] = listChengBanKeShi[valueCellKeShi].zhuGuanLingDao
    listShiFenFenLei[valueCellKeShi] = {}


for valueCellSheQu in listChengBanSheQu:
    listSheQu.append(valueCellSheQu)
    listKaoHeZhuan[valueCellSheQu] = 0
    listKaoHeZhi[valueCellSheQu] = 0
    listLianXiZhuan[valueCellSheQu] = 0
    listLianXiZhi[valueCellSheQu] = 0
    listJieJueZhuan[valueCellSheQu] = 0
    listJieJueZhi[valueCellSheQu] = 0
    listManYiZhuan[valueCellSheQu] = 0
    listManYiZhi[valueCellSheQu] = 0
    listJiBenZhuan[valueCellSheQu] = 0
    listJiBenZhi[valueCellSheQu] = 0
    listWeiLianXiZhuan[valueCellSheQu] = 0
    listWeiLianXiZhi[valueCellSheQu] = 0
    listWeiJieJueZhuan[valueCellSheQu] = 0
    listWeiJieJueZhi[valueCellSheQu] = 0
    listBuManYiZhuan[valueCellSheQu] = 0
    listBuManYiZhi[valueCellSheQu] = 0
    listJieDanZhi[valueCellSheQu] = 0
    listJieDanZhuan[valueCellSheQu] = 0
    listJianMianZhuan[valueCellSheQu] = 0
    listJianMianZhi[valueCellSheQu] =0
    listWeiJianMianZhuan[valueCellSheQu] = 0
    listWeiJianMianZhi[valueCellSheQu] = 0
    lvLianXiZhi[valueCellSheQu] = None
    lvWeiLianXiZhi[valueCellSheQu] = None
    lvJieJueZhi[valueCellSheQu] = None
    lvWeiJieJueZhi[valueCellSheQu] = None
    lvManYiZhi[valueCellSheQu] = None
    lvJiBenZhi[valueCellSheQu] = None
    lvBuManYiZhi[valueCellSheQu] = None
    lvManYiFinalZhi[valueCellSheQu] = None
    lvLianXiZhuan[valueCellSheQu] = None
    lvWeiLianXiZhuan[valueCellSheQu] = None
    lvJieJueZhuan[valueCellSheQu] = None
    lvWeiJieJueZhuan[valueCellSheQu] = None
    lvManYiZhuan[valueCellSheQu] = None
    lvJiBenZhuan[valueCellSheQu] = None
    lvBuManYiZhuan[valueCellSheQu] = None
    lvManYiFinalZhuan[valueCellSheQu] = None
    lvLianXiAverage[valueCellSheQu] = None
    lvWeiLianXiAverage[valueCellSheQu] = None
    lvJieJueAverage[valueCellSheQu] = None
    lvWeiJieJueAverage[valueCellSheQu] = None
    lvManYiAverage[valueCellSheQu] = None
    lvJiBenAverage[valueCellSheQu] = None
    lvBuManYiAverage[valueCellSheQu] = None
    lvManYiFinalAverage[valueCellSheQu] = None
    lvJianMianZhuan[valueCellSheQu] =None
    lvWeiJianMianZhuan[valueCellSheQu] = None
    lvJianMianZhi[valueCellSheQu] = None
    lvWeiJianMianZhi[valueCellSheQu] = None
    lvJianMianAverage[valueCellSheQu] = None
    lvWeiJianMianAverage[valueCellSheQu] = None
    deFenLuoFen[valueCellSheQu] = None
    shiFenLuoFen[valueCellSheQu] = None
    deFenLuoFen[valueCellSheQu] = None
    shiFenLuoFen[valueCellSheQu] = None
    # suoShuPianQu[valueCellSheQu] = listChengBanSheQu[valueCellSheQu].suoShuPianQu
    # fuZeRen[valueCellSheQu] = listChengBanSheQu[valueCellSheQu].fuZeRen
    # zhuGuanLingDao[valueCellSheQu] = listChengBanSheQu[valueCellSheQu].zhuGuanLingDao
    listShiFenFenLei[valueCellSheQu] = {}


logger.debug('承办部门-科室：{}'.format(listKeShi))
logger.debug('承办部门-社区/村：{}'.format(listSheQu))

colChengBanHuiFang = hanchxl.get_col_index(sheetHuiFang, '承办部门')
colLeiXingHuiFang = hanchxl.get_col_index(sheetHuiFang, '派单类型')
colHuiFangChuLi = hanchxl.get_col_index(sheetHuiFang, '是否处理')

maxRowHuiFang = sheetHuiFang.max_row
numWeiChuLi = 0
for lineIndexHuiFang in range(2, maxRowHuiFang+1):
    valueChengBanJieDan = sheetHuiFang.cell(lineIndexHuiFang, colChengBanHuiFang).value
    valueLeiXingJieDan = sheetHuiFang.cell(lineIndexHuiFang, colLeiXingHuiFang).value
    if valueChengBanJieDan is not None and valueLeiXingJieDan is not None:
        valueChengBanJieDan = valueChengBanJieDan.strip()
        valueLeiXingJieDan = valueLeiXingJieDan.strip()
        logger.debug('{} - {}'.format(valueChengBanJieDan, valueLeiXingJieDan))
        if valueChengBanJieDan in listKeShi or valueChengBanJieDan in listSheQu:
            if valueLeiXingJieDan == '直派' or valueLeiXingJieDan == '市派':
                listJieDanZhi[valueChengBanJieDan] = listJieDanZhi[valueChengBanJieDan] + 1
                sheetHuiFang.cell(lineIndexHuiFang, colHuiFangChuLi).value = '已处理-直派'
            else:
                if valueLeiXingJieDan == '转派' or valueLeiXingJieDan == '区派':
                    listJieDanZhuan[valueChengBanJieDan] = listJieDanZhuan[valueChengBanJieDan] + 1
                    sheetHuiFang.cell(lineIndexHuiFang, colHuiFangChuLi).value = '已处理-转派'
                else:
                    numWeiChuLi = numWeiChuLi + 1
                    sheetHuiFang.cell(lineIndexHuiFang, colHuiFangChuLi).value = '未处理'
        else:
            numWeiChuLi = numWeiChuLi + 1
            sheetHuiFang.cell(lineIndexHuiFang, colHuiFangChuLi).value = '未处理-无承办部门'
    else:
        numWeiChuLi = numWeiChuLi + 1
        sheetHuiFang.cell(lineIndexHuiFang, colHuiFangChuLi).value = '未处理-数据错误'

logger.debug('接单量-直派：{}'.format(listJieDanZhi))
logger.debug('接单量-转派：{}'.format(listJieDanZhuan))
# maxRowJieDan2 = sheetJieDan.max_row
if numWeiChuLi > 0:
    # 保存未匹配承办部门的表格
    bookJieDan.save(fileHuiFangNo)
    logger.debug('保存  {}'.format(fileHuiFangNo))

colChengBanIndex = hanchxl.get_col_index(sheetHuiFang, '承办部门')
colLeiXing = hanchxl.get_col_index(sheetHuiFang, '派单类型')
colLianXi = hanchxl.get_col_index(sheetHuiFang, '是否联系')
colJieJue = hanchxl.get_col_index(sheetHuiFang, '是否解决')
colManYi = hanchxl.get_col_index(sheetHuiFang, '是否满意')
colJianMian = hanchxl.get_col_index(sheetHuiFang, '是否见面')
colChuLi = hanchxl.get_col_index(sheetHuiFang, '是否处理')
colZhuangTai = hanchxl.get_col_index(sheetHuiFang, '回访状态')

logger.debug('承办部门：{}列  '.format(colChengBanIndex))
logger.debug('派单类型：{}列'.format(colLeiXing))
logger.debug('是否联系：{}列'.format(colLianXi))
logger.debug('是否解决：{}列'.format(colJieJue))
logger.debug('是否满意：{}列'.format(colManYi))
logger.debug('是否处理：{}列'.format(colChuLi))
logger.debug('是否见面：{}列'.format(colJianMian))
logger.debug('回访状态：{}列'.format(colZhuangTai))
lineIndex = 2
numWeiChuLiKaoHe = 0
countShuangMan = 0
countJieJueJiBenManYi = 0
countJieJueBuManYi = 0
countWeiJieJue = 0
maxRowHuiFang = sheetHuiFang.max_row

for lineIndex in range(2, maxRowHuiFang + 1):
    valueChengBanCur = sheetHuiFang.cell(lineIndex, colChengBanIndex).value
    valueLeiXing = sheetHuiFang.cell(lineIndex, colLeiXing).value
    valueLianxi = sheetHuiFang.cell(lineIndex, colLianXi).value
    valueJieJue = sheetHuiFang.cell(lineIndex, colJieJue).value
    valueManYi = sheetHuiFang.cell(lineIndex, colManYi).value
    valueJianMian = sheetHuiFang.cell(lineIndex, colJianMian).value
    valueZhuangTai = sheetHuiFang.cell(lineIndex, colZhuangTai).value
    if valueChengBanCur is not None and valueLeiXing is not None \
        and valueLianxi is not None and valueJieJue is not None and valueManYi is not None \
            and valueZhuangTai is not None and valueJianMian is not None:
        if valueZhuangTai.find('有效回访') == -1:
            continue
        valueChengBanCur = valueChengBanCur.strip()
        valueLeiXing = valueLeiXing.strip()
        valueLianxi = valueLianxi.strip()
        valueJieJue = valueJieJue.strip()
        valueManYi = valueManYi.strip()
        valueJianMian = valueJianMian.strip()
        valueZhuangTai = valueZhuangTai.strip()
        logger.debug('{} - {}'.format(valueChengBanCur, valueLeiXing))
        if valueChengBanCur in listKeShi or valueChengBanCur in listSheQu:
            if valueLeiXing == '市派' or valueLeiXing == '直派':
                listKaoHeZhi[valueChengBanCur] = listKaoHeZhi[valueChengBanCur] + 1
                if valueLianxi == '已联系' or valueLianxi == '联系':
                    listLianXiZhi[valueChengBanCur] = listLianXiZhi[valueChengBanCur] + 1
                if valueLianxi == '未联系':
                    listWeiLianXiZhi[valueChengBanCur] = listWeiLianXiZhi[valueChengBanCur] + 1
                if valueJieJue == '已解决' or valueJieJue == '解决':
                    listJieJueZhi[valueChengBanCur] = listJieJueZhi[valueChengBanCur] + 1
                if valueJieJue == '未解决':
                    listWeiJieJueZhi[valueChengBanCur] = listWeiJieJueZhi[valueChengBanCur] + 1
                if valueManYi == '满意' or valueManYi == '非常满意':
                    listManYiZhi[valueChengBanCur] = listManYiZhi[valueChengBanCur] + 1
                if valueManYi == '基本满意':
                    listJiBenZhi[valueChengBanCur] = listJiBenZhi[valueChengBanCur] + 1
                if valueManYi == '不满意' or valueManYi == '非常不满意':
                    listBuManYiZhi[valueChengBanCur] = listBuManYiZhi[valueChengBanCur] + 1
                if valueJianMian == '见面' or valueJianMian == '已见面':
                    listJianMianZhi[valueChengBanCur] = listJianMianZhi[valueChengBanCur] + 1
                if valueJianMian == '未见面':
                    listWeiJianMianZhi[valueChengBanCur] = listWeiJianMianZhi[valueChengBanCur] + 1
                # sheetKaoHe.delete_rows(lineIndex)
                # maxRowQuanBu = maxRowQuanBu - 1
                sheetKaoHe.cell(lineIndex, colChuLi).value = '已处理-直派'
            else:
                if valueLeiXing == '区派' or valueLeiXing == '转派':
                    listKaoHeZhuan[valueChengBanCur] = listKaoHeZhuan[valueChengBanCur] + 1
                    if valueLianxi == '已联系' or valueLianxi == '联系':
                        listLianXiZhuan[valueChengBanCur] = listLianXiZhuan[valueChengBanCur] + 1
                    if valueLianxi == '未联系':
                        listWeiLianXiZhuan[valueChengBanCur] = listWeiLianXiZhuan[valueChengBanCur] + 1
                    if valueJieJue == '已解决' or valueJieJue == '解决':
                        listJieJueZhuan[valueChengBanCur] = listJieJueZhuan[valueChengBanCur] + 1
                    if valueJieJue == '未解决':
                        listWeiJieJueZhuan[valueChengBanCur] = listWeiJieJueZhuan[valueChengBanCur] + 1
                    if valueManYi == '满意' or valueManYi == '非常满意':
                        listManYiZhuan[valueChengBanCur] = listManYiZhuan[valueChengBanCur] + 1
                    if valueManYi == '基本满意':
                        listJiBenZhuan[valueChengBanCur] = listJiBenZhuan[valueChengBanCur] + 1
                    if valueManYi == '不满意' or valueManYi == '非常不满意':
                        listBuManYiZhuan[valueChengBanCur] = listBuManYiZhuan[valueChengBanCur] + 1
                    if valueJianMian == '见面' or valueJianMian == '已见面':
                        listJianMianZhuan[valueChengBanCur] = listJianMianZhuan[valueChengBanCur] + 1
                    if valueJianMian == '未见面':
                        listWeiJianMianZhuan[valueChengBanCur] = listWeiJianMianZhuan[valueChengBanCur] + 1
                    sheetHuiFang.cell(lineIndex, colChuLi).value = '已处理-转派'
                else:
                    sheetHuiFang.cell(lineIndex, colChuLi).value = '未处理'
            if valueJieJue == '解决' and valueManYi == '满意':
                countShuangMan = countShuangMan + 1
            elif valueJieJue == '解决' and valueManYi == '不满意':
                countJieJueBuManYi = countJieJueBuManYi + 1
            elif valueJieJue == '解决' and valueManYi == '基本满意':
                countJieJueJiBenManYi = countJieJueJiBenManYi + 1
            elif valueJieJue == '未解决':
                countWeiJieJue = countWeiJieJue +1
        else:
            sheetHuiFang.cell(lineIndex, colChuLi).value = ' 未处理 - 无承办部门'
            numWeiChuLiKaoHe = numWeiChuLiKaoHe + 1
    else:
        sheetHuiFang.cell(lineIndex, colChuLi).value = ' 未处理 - 数据错误'
        numWeiChuLiKaoHe = numWeiChuLiKaoHe + 1

logger.debug('考核-转派：{}'.format(listKaoHeZhuan))
logger.debug('考核-直派：{}'.format(listKaoHeZhi))
logger.debug('联系-转派：{}'.format(listLianXiZhuan))
logger.debug('联系-直派：{}'.format(listLianXiZhi))
logger.debug('未联系-转派：{}'.format(listWeiLianXiZhuan))
logger.debug('未联系-直派：{}'.format(listWeiLianXiZhi))
logger.debug('解决-转派：{}'.format(listJieJueZhuan))
logger.debug('解决-直派：{}'.format(listJieJueZhi))
logger.debug('未解决-转派：{}'.format(listWeiJieJueZhuan))
logger.debug('未解决-直派：{}'.format(listWeiJieJueZhi))
logger.debug('满意-转派：{}'.format(listManYiZhuan))
logger.debug('满意-直派：{}'.format(listManYiZhi))
logger.debug('基本满意-转派：{}'.format(listJiBenZhuan))
logger.debug('基本满意-直派：{}'.format(listJiBenZhi))
logger.debug('不满意-转派：{}'.format(listBuManYiZhuan))
logger.debug('不满意-直派：{}'.format(listBuManYiZhi))
logger.debug('见面-直派：{}'.format(listJianMianZhi))
logger.debug('见面-转派：{}'.format(listJianMianZhuan))
logger.debug('未见面-直派：{}'.format(listWeiJianMianZhi))
logger.debug('未见面-转派：{}'.format(listWeiJianMianZhuan))

countJieDanZhi = 0
countJieDanZhuan = 0
countKaoHeZhi = 0
countKaoHeZhuan = 0
countLianXiZhi = 0
countLianXiZhuan = 0
countWeiLianXiZhi = 0
countWeiLianXiZhuan = 0
countJieJueZhi = 0
countJieJueZhuan = 0
countWeiJieJueZhi = 0
countWeiJieJueZhuan = 0
countManYiZhi = 0
countManYiZhuan = 0
countJianMianZhi = 0
countJianMianZhuan = 0
countWeiJianMianZhi = 0
countWeiJianMianZhuan = 0
countJiBenZhi = 0
countJIBenZhuan = 0
countBuManYiZhi = 0
countBuManYiZhuan = 0
countKaoHeZhiKeShi = 0
countKaoHeZhuanKeShi = 0
countKaoHeZhiSheQu = 0
countKaoHeZhuanSheQu = 0

for indexTongJi in range(0, len(listKeShi)):
    nameTongJi = listKeShi[indexTongJi]
    countJieDanZhi = countJieDanZhi + listJieDanZhi[nameTongJi]
    countJieDanZhuan = countJieDanZhuan + listJieDanZhuan[nameTongJi]
    countKaoHeZhi = countKaoHeZhi + listKaoHeZhi[nameTongJi]
    countKaoHeZhiKeShi = countKaoHeZhiKeShi + listKaoHeZhi[nameTongJi]
    countKaoHeZhuan = countKaoHeZhuan + listKaoHeZhuan[nameTongJi]
    countKaoHeZhuanKeShi = countKaoHeZhuanKeShi + listKaoHeZhuan[nameTongJi]
    countLianXiZhi = countLianXiZhi + listLianXiZhi[nameTongJi]
    countLianXiZhuan = countLianXiZhuan + listLianXiZhuan[nameTongJi]
    countWeiLianXiZhi = countWeiLianXiZhi + listWeiLianXiZhi[nameTongJi]
    countWeiLianXiZhuan = countWeiLianXiZhuan + listWeiLianXiZhuan[nameTongJi]
    countJieJueZhi = countJieJueZhi + listJieJueZhi[nameTongJi]
    countJieJueZhuan = countJieJueZhuan + listJieJueZhuan[nameTongJi]
    countWeiJieJueZhi = countWeiJieJueZhi + listWeiJieJueZhi[nameTongJi]
    countWeiJieJueZhuan = countWeiJieJueZhuan + listWeiJieJueZhuan[nameTongJi]
    countManYiZhi = countManYiZhi + listManYiZhi[nameTongJi]
    countManYiZhuan = countManYiZhuan + listManYiZhuan[nameTongJi]
    countJiBenZhi = countJiBenZhi + listJiBenZhi[nameTongJi]
    countJIBenZhuan = countJIBenZhuan + listJiBenZhuan[nameTongJi]
    countBuManYiZhi = countBuManYiZhi + listBuManYiZhi[nameTongJi]
    countBuManYiZhuan = countBuManYiZhuan + listBuManYiZhuan[nameTongJi]
    countJianMianZhi = countJianMianZhi + listJianMianZhi[nameTongJi]
    countJianMianZhuan = countJianMianZhuan + listJianMianZhuan[nameTongJi]
    countWeiJianMianZhi = countWeiJianMianZhi + listWeiJianMianZhi[nameTongJi]
    countWeiJianMianZhuan = countWeiJianMianZhuan + listWeiJianMianZhuan[nameTongJi]


for indexTongJi in range(0, len(listSheQu)):
    nameTongJi = listSheQu[indexTongJi]
    countJieDanZhi = countJieDanZhi + listJieDanZhi[nameTongJi]
    countJieDanZhuan = countJieDanZhuan + listJieDanZhuan[nameTongJi]
    countKaoHeZhi = countKaoHeZhi + listKaoHeZhi[nameTongJi]
    countKaoHeZhiSheQu = countKaoHeZhiSheQu + listKaoHeZhi[nameTongJi]
    countKaoHeZhuan = countKaoHeZhuan + listKaoHeZhuan[nameTongJi]
    countKaoHeZhuanSheQu = countKaoHeZhuanSheQu + listKaoHeZhuan[nameTongJi]
    countLianXiZhi = countLianXiZhi + listLianXiZhi[nameTongJi]
    countLianXiZhuan = countLianXiZhuan + listLianXiZhuan[nameTongJi]
    countWeiLianXiZhi = countWeiLianXiZhi + listWeiLianXiZhi[nameTongJi]
    countWeiLianXiZhuan = countWeiLianXiZhuan + listWeiLianXiZhuan[nameTongJi]
    countJieJueZhi = countJieJueZhi + listJieJueZhi[nameTongJi]
    countJieJueZhuan = countJieJueZhuan + listJieJueZhuan[nameTongJi]
    countWeiJieJueZhi = countWeiJieJueZhi + listWeiJieJueZhi[nameTongJi]
    countWeiJieJueZhuan = countWeiJieJueZhuan + listWeiJieJueZhuan[nameTongJi]
    countManYiZhi = countManYiZhi + listManYiZhi[nameTongJi]
    countManYiZhuan = countManYiZhuan + listManYiZhuan[nameTongJi]
    countJiBenZhi = countJiBenZhi + listJiBenZhi[nameTongJi]
    countJIBenZhuan = countJIBenZhuan + listJiBenZhuan[nameTongJi]
    countBuManYiZhi = countBuManYiZhi + listBuManYiZhi[nameTongJi]
    countBuManYiZhuan = countBuManYiZhuan + listBuManYiZhuan[nameTongJi]
    countJianMianZhi = countJianMianZhi + listJianMianZhi[nameTongJi]
    countJianMianZhuan = countJianMianZhuan + listJianMianZhuan[nameTongJi]
    countWeiJianMianZhi = countWeiJianMianZhi + listWeiJianMianZhi[nameTongJi]
    countWeiJianMianZhuan = countWeiJianMianZhuan + listWeiJianMianZhuan[nameTongJi]

logger.debug('考核-转派：{}'.format(countKaoHeZhuan))
logger.debug('联系-转派：{}'.format(countLianXiZhuan))
logger.debug('未联系-转派：{}'.format(countWeiLianXiZhuan))
logger.debug('解决-转派：{}'.format(countJieJueZhuan))
logger.debug('未解决-转派：{}'.format(countWeiJieJueZhuan))
logger.debug('满意-转派：{}'.format(countManYiZhuan))
logger.debug('基本满意-转派：{}'.format(countJIBenZhuan))
logger.debug('不满意-转派：{}'.format(countBuManYiZhuan))
logger.debug('见面-转派：{}'.format(countJianMianZhuan))
logger.debug('未见面-转派：{}'.format(countWeiJianMianZhuan))

logger.debug('考核-直派：{}'.format(countKaoHeZhi))
logger.debug('联系-直派：{}'.format(countLianXiZhi))
logger.debug('未联系-直派：{}'.format(countWeiLianXiZhi))
logger.debug('解决-直派：{}'.format(countJieJueZhi))
logger.debug('未解决-直派：{}'.format(countWeiJieJueZhi))
logger.debug('满意-直派：{}'.format(countManYiZhi))
logger.debug('基本满意-直派：{}'.format(countJiBenZhi))
logger.debug('不满意-直派：{}'.format(countBuManYiZhi))
logger.debug('见面-直派：{}'.format(countJianMianZhi))
logger.debug('未见面-直派：{}'.format(countWeiJianMianZhi))


lvLianxiCount = 0
lvLianxiCountZhuan = 0
lvLianxiCountZhi = 0

lvJieJueCount = 0
lvJieJueCountZhuan = 0
lvJieJueCountZhi = 0

lvJianMianCount = 0
lvJianMianCountZhuan = 0
lvJianMianCountZhi = 0

lvManYiCount = 0
lvManYiCountZhuan = 0
lvManYiCountZhi = 0

lvJiBenCount = 0
lvJiBenCountZhuan = 0
lvJiBenCountZhi = 0

lvManYiFinallCountZhi = 0
lvManYiFinallCountZhuan = 0

luoFenZhuan = 0
if countKaoHeZhuan > 0:
    lvLianxiCountZhuan = round(countLianXiZhuan/countKaoHeZhuan*100, 2)
    lvJieJueCountZhuan = round(countJieJueZhuan/countKaoHeZhuan*100, 2)
    lvManYiCountZhuan = round(countManYiZhuan/countKaoHeZhuan*100, 2)
    lvJianMianCountZhuan = round(countJianMianZhuan/countKaoHeZhuan * 100, 2)
    lvJiBenCountZhuan = round(countJIBenZhuan/countKaoHeZhuan*100, 2)
    lvManYiFinallCountZhuan = round(lvManYiCountZhuan+lvJiBenCountZhuan*0.8, 2)
    luoFenZhuan = round(lvLianxiCountZhuan*0.1 + lvJieJueCountZhuan*0.5 + lvManYiFinallCountZhuan*0.4, 2)
logger.debug('区派三率 考核{}件   响应率：{}  解决率：{}  满意率：{} 见面率：{}'.format(countKaoHeZhuan, lvLianxiCountZhuan
                                                                                    , lvJieJueCountZhuan
                                                                                    , lvManYiFinallCountZhuan
                                                                                    , lvJianMianCountZhuan))
luoFenZhi = 0
if countKaoHeZhi > 0:
    lvLianxiCountZhi = round(countLianXiZhi/countKaoHeZhi*100, 2)
    lvJieJueCountZhi = round(countJieJueZhi/countKaoHeZhi*100, 2)
    lvManYiCountZhi = round(countManYiZhi/countKaoHeZhi*100, 2)
    lvJianMianCountZhi = round(countJianMianZhi/countKaoHeZhi * 100, 2)
    lvJiBenCountZhi = round(countJiBenZhi/countKaoHeZhi*100, 2)
    lvManYiFinallCountZhi = round(lvManYiCountZhi+lvJiBenCountZhi*0.8, 2)
    luoFenZhi = round(lvLianxiCountZhi * 0.1 + lvJieJueCountZhi * 0.5 + lvManYiFinallCountZhi * 0.4, 2)
logger.debug('市派三率 考核{}件  响应率：{}  解决率：{}  满意率：{} 见面率：{}'.format(countKaoHeZhi, lvLianxiCountZhi
                                                                                   , lvJieJueCountZhi
                                                                                   , lvManYiFinallCountZhi
                                                                                   , lvJianMianCountZhi))

if countKaoHeZhuan > 0 and countKaoHeZhi > 0:
    lvLianxiCount = round((lvLianxiCountZhuan + lvLianxiCountZhi)/2, 2)
    lvJieJueCount = round((lvJieJueCountZhuan + lvJieJueCountZhi)/2, 2)
    lvManYiCount = round((lvManYiCountZhuan + lvManYiCountZhi) / 2, 2)
    lvJiBenCount = round((lvJiBenCountZhuan + lvJiBenCountZhi) / 2, 2)
    lvJianMianCount = round((lvJianMianCountZhuan + lvJianMianCountZhi) / 2, 2)
elif countKaoHeZhuan > 0:
    lvLianxiCount = round(lvLianxiCountZhuan, 2)
    lvJieJueCount = round(lvJieJueCountZhuan, 2)
    lvManYiCount = round(lvManYiCountZhuan, 2)
    lvJiBenCount = round(lvJiBenCountZhuan, 2)
    lvJianMianCount = round(lvJianMianCountZhuan, 2)
else:
    lvLianxiCount = round(lvLianxiCountZhi, 2)
    lvJieJueCount = round(lvJieJueCountZhi, 2)
    lvManYiCount = round(lvManYiCountZhi, 2)
    lvJiBenCount = round(lvJiBenCountZhi, 2)
    lvJianMianCount = round(lvJianMianCountZhi, 2)

lvManYiFinalCount = round(lvManYiCount + lvJiBenCount*0.8, 2)
logger.debug('区级有效考核{}件 响应率：{}  解决率：{}  满意率：{} 见面率：{}'.format(countKaoHeZhuan + countKaoHeZhi
                                                                                 , lvLianxiCount, lvJieJueCount
                                                                                 , lvManYiFinalCount, lvJianMianCount))
luoFen = round(lvLianxiCount*0.1 + lvJieJueCount*0.5 + lvManYiFinalCount*0.4, 2)
logger.debug('裸分：{}  裸分(四舍五入)：{}'.format(luoFen, round(luoFen)))


def make_pai_ming_shu_ju(list_src):
    for rowIndex in range(0, len(list_src)):
        buMenName = list_src[rowIndex]
        if listKaoHeZhi[buMenName] != 0:
            lvLianXiZhi[buMenName] = round(listLianXiZhi[buMenName]/listKaoHeZhi[buMenName]*100)
            lvWeiLianXiZhi[buMenName] = round(listWeiLianXiZhi[buMenName]/listKaoHeZhi[buMenName]*100)
            lvJieJueZhi[buMenName] = round(listJieJueZhi[buMenName] / listKaoHeZhi[buMenName]*100)
            lvWeiJieJueZhi[buMenName] = round(listWeiJieJueZhi[buMenName] / listKaoHeZhi[buMenName] * 100)
            lvManYiZhi[buMenName] = round(listManYiZhi[buMenName] / listKaoHeZhi[buMenName]*100)
            lvJiBenZhi[buMenName] = round(listJiBenZhi[buMenName] / listKaoHeZhi[buMenName]*100)
            lvBuManYiZhi[buMenName] = round(listBuManYiZhi[buMenName] / listKaoHeZhi[buMenName] * 100)
            lvManYiFinalZhi[buMenName] = round(lvManYiZhi[buMenName] + lvJiBenZhi[buMenName]*0.8)
            lvJianMianZhi[buMenName] = round(listJianMianZhi[buMenName] / listKaoHeZhi[buMenName] * 100)
            lvWeiJianMianZhi[buMenName] = round(listWeiJianMianZhi[buMenName] / listKaoHeZhi[buMenName] * 100)
        if listKaoHeZhuan[buMenName] != 0:
            lvLianXiZhuan[buMenName] = round(listLianXiZhuan[buMenName]/listKaoHeZhuan[buMenName]*100)
            lvWeiLianXiZhuan[buMenName] = round(listWeiLianXiZhuan[buMenName]/listKaoHeZhuan[buMenName]*100)
            lvJieJueZhuan[buMenName] = round(listJieJueZhuan[buMenName]/listKaoHeZhuan[buMenName]*100)
            lvWeiJieJueZhuan[buMenName] = round(listWeiJieJueZhuan[buMenName] / listKaoHeZhuan[buMenName] * 100)
            lvManYiZhuan[buMenName] = round(listManYiZhuan[buMenName]/listKaoHeZhuan[buMenName]*100)
            lvJiBenZhuan[buMenName] = round(listJiBenZhuan[buMenName]/listKaoHeZhuan[buMenName]*100)
            lvBuManYiZhuan[buMenName] = round(listBuManYiZhuan[buMenName] / listKaoHeZhuan[buMenName] * 100)
            lvManYiFinalZhuan[buMenName] = round(lvManYiZhuan[buMenName] + lvJiBenZhuan[buMenName] * 0.8)
            lvJianMianZhuan[buMenName] = round(listJianMianZhuan[buMenName] / listKaoHeZhuan[buMenName] * 100)
            lvWeiJianMianZhuan[buMenName] = round(listWeiJianMianZhuan[buMenName] / listKaoHeZhuan[buMenName] * 100)
        # 联系率计算平均值
        deFenLianXi = 0
        shiFenLianXi = 0
        deFenJieJue = 0
        shiFenJieJue = 0
        deFenShangMan = 0
        deFenJiBen = 0
        ShiFenBuManYi = 0
        if countKaoHeZhuan > 0 and countKaoHeZhi > 0:
            deFenLianXi = round((listLianXiZhi[buMenName]/countKaoHeZhi*100
                                 + listLianXiZhuan[buMenName]/countKaoHeZhuan*100)/2, 2)
            shiFenLianXi = round((listWeiLianXiZhi[buMenName]/countKaoHeZhi*100
                                 + listWeiLianXiZhuan[buMenName]/countKaoHeZhuan*100)/2, 2)
            deFenJieJue = round((listJieJueZhi[buMenName]/countKaoHeZhi*100
                                 + listJieJueZhuan[buMenName]/countKaoHeZhuan*100)/2, 2)
            shiFenJieJue = round((listWeiJieJueZhi[buMenName]/countKaoHeZhi*100
                                 + listWeiJieJueZhuan[buMenName]/countKaoHeZhuan*100)/2, 2)
            deFenShangMan = round((listManYiZhi[buMenName]/countKaoHeZhi*100
                                   + listManYiZhuan[buMenName]/countKaoHeZhuan*100)/2, 2)
            deFenJiBen = round((listJiBenZhi[buMenName]/countKaoHeZhi*100
                                + listJiBenZhuan[buMenName]/countKaoHeZhuan*100)/2, 2)
            ShiFenBuManYi = round((listBuManYiZhi[buMenName]/countKaoHeZhi*100
                                   + listBuManYiZhuan[buMenName]/countKaoHeZhuan*100)/2, 2)
        elif countKaoHeZhuan > 0:
            deFenLianXi = round(listLianXiZhuan[buMenName]/countKaoHeZhuan*100, 2)
            shiFenLianXi = round(listWeiLianXiZhuan[buMenName]/countKaoHeZhuan*100, 2)
            deFenJieJue = round(listJieJueZhuan[buMenName]/countKaoHeZhuan*100, 2)
            shiFenJieJue = round(listWeiJieJueZhuan[buMenName]/countKaoHeZhuan*100, 2)
            deFenShangMan = round(listManYiZhuan[buMenName]/countKaoHeZhuan*100, 2)
            deFenJiBen = round(listJiBenZhuan[buMenName]/countKaoHeZhuan*100, 2)
            ShiFenBuManYi = round(listBuManYiZhuan[buMenName]/countKaoHeZhuan*100, 2)
        elif countKaoHeZhi > 0:
            deFenLianXi = round(listLianXiZhi[buMenName]/countKaoHeZhi*100, 2)
            shiFenLianXi = round(listWeiLianXiZhi[buMenName]/countKaoHeZhi*100, 2)
            deFenJieJue = round(listJieJueZhi[buMenName]/countKaoHeZhi*100, 2)
            shiFenJieJue = round(listWeiJieJueZhi[buMenName]/countKaoHeZhi*100, 2)
            deFenShangMan = round(listManYiZhi[buMenName]/countKaoHeZhi*100, 2)
            deFenJiBen = round(listJiBenZhi[buMenName]/countKaoHeZhi*100, 2)
            ShiFenBuManYi = round(listBuManYiZhi[buMenName]/countKaoHeZhi*100, 2)

        deFenManYi = round(deFenShangMan + deFenJiBen*0.8, 2)
        shiFenManYi = round(ShiFenBuManYi + deFenJiBen*0.2, 2)
        deFenLuoFen[buMenName] = round(deFenLianXi*0.1+deFenJieJue*0.5+deFenManYi*0.4, 2)
        shiFenLuoFen[buMenName] = round(shiFenLianXi*0.1+shiFenJieJue*0.5+shiFenManYi*0.4, 2)

        if lvLianXiZhi[buMenName] is not None and lvLianXiZhuan[buMenName] is not None:
            lvLianXiAverage[buMenName] = round((lvLianXiZhi[buMenName] + lvLianXiZhuan[buMenName]) / 2)
        else:
            if lvLianXiZhi[buMenName] is not None:
                lvLianXiAverage[buMenName] = lvLianXiZhi[buMenName]
            if lvLianXiZhuan[buMenName] is not None:
                lvLianXiAverage[buMenName] = lvLianXiZhuan[buMenName]

        # 未联系率计算平均值
        if lvWeiLianXiZhi[buMenName] is not None and lvWeiLianXiZhuan[buMenName] is not None:
            lvWeiLianXiAverage[buMenName] = round((lvLianXiZhi[buMenName] + lvWeiLianXiZhuan[buMenName]) / 2)
        else:
            if lvWeiLianXiZhi[buMenName] is not None:
                lvWeiLianXiAverage[buMenName] = lvWeiLianXiZhi[buMenName]
            if lvWeiLianXiZhuan[buMenName] is not None:
                lvWeiLianXiAverage[buMenName] = lvWeiLianXiZhuan[buMenName]

        # 解决率计算平均值
        if lvJieJueZhi[buMenName] is not None and lvJieJueZhuan[buMenName] is not None:
            lvJieJueAverage[buMenName] = round((lvJieJueZhi[buMenName] + lvJieJueZhuan[buMenName]) / 2)
        else:
            if lvJieJueZhi[buMenName] is not None:
                lvJieJueAverage[buMenName] = lvJieJueZhi[buMenName]
            if lvJieJueZhuan[buMenName] is not None:
                lvJieJueAverage[buMenName] = lvJieJueZhuan[buMenName]

        # 未解决率计算平均值
        if lvWeiJieJueZhi[buMenName] is not None and lvWeiJieJueZhuan[buMenName] is not None:
            lvWeiJieJueAverage[buMenName] = round((lvWeiJieJueZhi[buMenName] + lvWeiJieJueZhuan[buMenName]) / 2)
        else:
            if lvWeiJieJueZhi[buMenName] is not None:
                lvWeiJieJueAverage[buMenName] = lvWeiJieJueZhi[buMenName]
            if lvWeiJieJueZhuan[buMenName] is not None:
                lvWeiJieJueAverage[buMenName] = lvWeiJieJueZhuan[buMenName]

        # 满意率计算平均值
        if lvManYiZhi[buMenName] is not None and lvManYiZhuan[buMenName] is not None:
            lvManYiAverage[buMenName] = round((lvManYiZhi[buMenName] + lvManYiZhuan[buMenName]) / 2)
        else:
            if lvManYiZhi[buMenName] is not None:
                lvManYiAverage[buMenName] = lvManYiZhi[buMenName]
            if lvManYiZhuan[buMenName] is not None:
                lvManYiAverage[buMenName] = lvManYiZhuan[buMenName]

        # 基本满意率计算平均值
        if lvJiBenZhi[buMenName] is not None and lvJiBenZhuan[buMenName] is not None:
            lvJiBenAverage[buMenName] = round((lvJiBenZhi[buMenName] + lvJiBenZhuan[buMenName]) / 2)
        else:
            if lvJiBenZhi[buMenName] is not None:
                lvJiBenAverage[buMenName] = lvJiBenZhi[buMenName]
            if lvJiBenZhuan[buMenName] is not None:
                lvJiBenAverage[buMenName] = lvJiBenZhuan[buMenName]

        # 不满意率计算平均值
        if lvBuManYiZhi[buMenName] is not None and lvBuManYiZhuan[buMenName] is not None:
            lvBuManYiAverage[buMenName] = round((lvBuManYiZhi[buMenName] + lvBuManYiZhuan[buMenName]) / 2)
        else:
            if lvBuManYiZhi[buMenName] is not None:
                lvBuManYiAverage[buMenName] = lvBuManYiZhi[buMenName]
            if lvBuManYiZhuan[buMenName] is not None:
                lvBuManYiAverage[buMenName] = lvBuManYiZhuan[buMenName]

        # 最终满意率计算平均值
        if lvManYiFinalZhi[buMenName] is not None and lvManYiFinalZhuan[buMenName] is not None:
            lvManYiFinalAverage[buMenName] = round((lvManYiFinalZhi[buMenName] + lvManYiFinalZhuan[buMenName]) / 2)
        else:
            if lvManYiFinalZhi[buMenName] is not None:
                lvManYiFinalAverage[buMenName] = lvManYiFinalZhi[buMenName]
            if lvManYiFinalZhuan[buMenName] is not None:
                lvManYiFinalAverage[buMenName] = lvManYiFinalZhuan[buMenName]
        # 见面率计算平均值
        if lvJianMianZhi[buMenName] is not None and lvJianMianZhuan[buMenName] is not None:
            lvJianMianAverage[buMenName] = round((lvJianMianZhi[buMenName] + lvJianMianZhuan[buMenName]) / 2)
        else:
            if lvJianMianZhi[buMenName] is not None:
                lvJianMianAverage[buMenName] = lvJianMianZhi[buMenName]
            if lvJianMianZhuan[buMenName] is not None:
                lvJianMianAverage[buMenName] = lvJianMianZhuan[buMenName]
        # 未见面率计算平均值
        if lvWeiJianMianZhi[buMenName] is not None and lvWeiJianMianZhuan[buMenName] is not None:
            lvWeiJianMianAverage[buMenName] = round((lvWeiJianMianZhi[buMenName] + lvWeiJianMianZhuan[buMenName]) / 2)
        else:
            if lvWeiJianMianZhi[buMenName] is not None:
                lvWeiJianMianAverage[buMenName] = lvWeiJianMianZhi[buMenName]
            if lvWeiJianMianZhuan[buMenName] is not None:
                lvWeiJianMianAverage[buMenName] = lvWeiJianMianZhuan[buMenName]

        logger.debug('{} 直派 考核：{}  联系：{} 联系率：{} 解决：{} 解决率：{} 满意：{}  基本满意：{} 满意率：{} '
                     '见面：{}  未见面：{} 见面率：{}'
                     .format(buMenName, listKaoHeZhi[buMenName], listLianXiZhi[buMenName], lvLianXiZhi[buMenName]
                             , listJieJueZhi[buMenName], lvJieJueZhi[buMenName]
                             , listManYiZhi[buMenName], listJiBenZhi[buMenName], lvManYiFinalZhi[buMenName]
                             , listJianMianZhi[buMenName], listWeiJianMianZhi[buMenName], lvJianMianZhi[buMenName]))
        logger.debug('{} 转派 考核：{}  联系：{} 联系率：{} 解决：{} 解决率：{} 满意：{}  基本满意：{} 满意率：{}'
                     '见面：{}  未见面：{} 见面率：{}'
                     .format(buMenName, listKaoHeZhuan[buMenName], listLianXiZhuan[buMenName], lvLianXiZhuan[buMenName]
                             , listJieJueZhuan[buMenName], lvJieJueZhuan[buMenName]
                             , listManYiZhuan[buMenName], listJiBenZhuan[buMenName], lvManYiFinalZhuan[buMenName]
                             , listJianMianZhuan[buMenName], listWeiJianMianZhuan[buMenName], lvJianMianZhuan[buMenName]))
        logger.debug('{} 平均  联系率：{}  解决率：{} 满意率：{} 见面率：{}'
                     .format(buMenName, lvLianXiAverage[buMenName], lvJieJueAverage[buMenName],
                             lvManYiFinalAverage[buMenName], lvJianMianAverage[buMenName]))


make_pai_ming_shu_ju(listKeShi)
make_pai_ming_shu_ju(listSheQu)


# '拱辰街道科室诉求总量反映情况统计（2022年10月19日0时至10月31日9时）'
strTitleKeShi = '拱辰街道科室已办结工单回访情况\r\n（'
strTitleKeShi = strTitleKeShi + '{}年{}月{}日9时至'.format(datatimeYesterday.year, datatimeYesterday.month, datatimeYesterday.day)
strTitleKeShi = strTitleKeShi + '{}月{}日9时)'.format(timeCur.month, timeCur.day)
sheetKeShiResult.cell(1, 1).value = strTitleKeShi

strMain = '{}年{}月{}日9时至'.format(datatimeYesterday.year, datatimeYesterday.month, datatimeYesterday.day)
strMain = strMain + '{}月{}日9时，'.format(timeCur.month, timeCur.day)

# 拱辰街道社区、村诉求考核反映情况统计（2022年9月19日0时至10月8日12时）
strTitleSheQu = '拱辰街道社区（村）已办结工单回访情况\r\n（'
strTitleSheQu = strTitleSheQu + '{}年{}月{}日9时至'.format(datatimeYesterday.year, datatimeYesterday.month, datatimeYesterday.day)
strTitleSheQu = strTitleSheQu + '{}月{}日9时)'.format(timeCur.month, timeCur.day)
sheetSheQuResult.cell(1, 1).value = strTitleSheQu

strMain = strMain + '我街道市民诉求处置中心接收诉求{}件'.format(countJieDanZhi+countJieDanZhuan)
strMain = strMain + '（区派{}件，市派{}件），'.format(countJieDanZhuan, countJieDanZhi)
strMain = strMain + '纳入考核{}件'.format(countKaoHeZhi+countKaoHeZhuan)
strMain = strMain + '（区派{}件，市派{}件），'.format(countKaoHeZhuan, countKaoHeZhi)
strMain = strMain + '联系{}件，'.format(countLianXiZhi+countLianXiZhuan)
strMain = strMain + '未联系{}件，'.format(countWeiLianXiZhi+countWeiLianXiZhuan)
strMain = strMain + '联系率{}%；'.format(round(lvLianxiCount))
strMain = strMain + '解决{}件，'.format(countJieJueZhi+countJieJueZhuan)
strMain = strMain + '未解决{}件，'.format(countWeiJieJueZhi+countWeiJieJueZhuan)
strMain = strMain + '解决率{}%；'.format(round(lvJieJueCount))
strMain = strMain + '满意{}件，'.format(countManYiZhi+countManYiZhuan)
strMain = strMain + '基本满意{}件，'.format(countJiBenZhi+countJIBenZhuan)
strMain = strMain + '不满意{}件，'.format(countBuManYiZhi+countBuManYiZhuan)
strMain = strMain + '满意率{}%；'.format(round(lvManYiFinalCount))
strMain = strMain + '见面{}件，'.format(countJianMianZhi+countJianMianZhuan)
strMain = strMain + '未见面{}件，'.format(countWeiJianMianZhi+countWeiJianMianZhuan)
strMain = strMain + '见面率{}%。'.format(round(lvJianMianCount))

logger.debug("{}".format(strMain))


def insert_into_sheet(sheet_insert, insert_index, inset_bu_men, de_fen, is_she_qu=0):
    rowInsert = insert_index
    sheet_insert.cell(rowInsert, 2).value = inset_bu_men            # 承办部门
    logger.debug('{} 接单量：{}  转派：{}   '
                 '直派：{} '.format(inset_bu_men, listJieDanZhi[inset_bu_men] + listJieDanZhuan[inset_bu_men]
                                   , listJieDanZhuan[inset_bu_men], listJieJueZhi[inset_bu_men]))
    if is_she_qu == 0:
        listJieDanKeShi[inset_bu_men] = listJieDanZhi[inset_bu_men] + listJieDanZhuan[inset_bu_men]
        listShiFenKeshi[inset_bu_men] = shiFenLuoFen[inset_bu_men]
    else:
        listJieDanSheQu[inset_bu_men] = listJieDanZhi[inset_bu_men] + listJieDanZhuan[inset_bu_men]
        listShiFenSheQu[inset_bu_men] = shiFenLuoFen[inset_bu_men]

    if listJieDanZhi[inset_bu_men] + listJieDanZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, 3).value = listJieDanZhi[inset_bu_men] + listJieDanZhuan[inset_bu_men]  # 接单总量(件)-转派
    if listJieDanZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, 4).value = listJieDanZhuan[inset_bu_men]  # 接单总量(件)-转派
    if listJieDanZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, 5).value = listJieDanZhi[inset_bu_men]  # 接单总量(件)-直派
    if listKaoHeZhi[inset_bu_men] + listKaoHeZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, 6).value = listKaoHeZhi[inset_bu_men] + listKaoHeZhuan[inset_bu_men]  # 纳入考核(件)-总量
    if listKaoHeZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhuanKaoHe).value = listKaoHeZhuan[inset_bu_men]  # 转派-考核
    if listLianXiZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhuanLian).value = listLianXiZhuan[inset_bu_men]  # 转派-联系
    if listWeiLianXiZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhuanLianNo).value = listWeiLianXiZhuan[inset_bu_men]  # 转派-未联系
    if listJieJueZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhuanJie).value = listJieJueZhuan[inset_bu_men]  # 转派-解决
    if listWeiJieJueZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhuanJieNo).value = listWeiJieJueZhuan[inset_bu_men]  # 转派-未解决
    if listManYiZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhuanMan).value = listManYiZhuan[inset_bu_men]  # 转派-满意
    if listJiBenZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhuanJiBen).value = listJiBenZhuan[inset_bu_men]  # 转派-基本满意
    if listBuManYiZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhuanManNo).value = listBuManYiZhuan[inset_bu_men]  # 转派-不满意
    if listJianMianZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhuanJian).value = listJianMianZhuan[inset_bu_men]  # 转派-见面
    if listWeiJianMianZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhuanJianNo).value = listWeiJianMianZhuan[inset_bu_men]  # 转派-未见面
    if lvLianXiZhuan[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colZhuanlvLian).value = lvLianXiZhuan[inset_bu_men]  # 转派-响应率
    if lvJieJueZhuan[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colZhuanLvJie).value = lvJieJueZhuan[inset_bu_men]  # 转派-解决率
    if lvManYiFinalZhuan[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colZhuanLvMan).value = lvManYiFinalZhuan[inset_bu_men]  # 转派-满意率
    if lvJianMianZhuan[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colZhuanLvJian).value = lvJianMianZhuan[inset_bu_men]  # 转派-见面率
    if listKaoHeZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhiKaoHe).value = listKaoHeZhi[inset_bu_men]  # 直派-考核
    if listLianXiZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhiLian).value = listLianXiZhi[inset_bu_men]  # 直派-联系
    if listWeiLianXiZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhiLianNo).value = listWeiLianXiZhi[inset_bu_men]  # 直派-未联系
    if listJieJueZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhiJie).value = listJieJueZhi[inset_bu_men]  # 直派-解决
    if listWeiJieJueZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhiJieNo).value = listWeiJieJueZhi[inset_bu_men]  # 直派-未解决
    if listManYiZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhiMan).value = listManYiZhi[inset_bu_men]  # 直派-满意
    if listJiBenZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhiJiBen).value = listJiBenZhi[inset_bu_men]  # 直派-基本满意
    if listBuManYiZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhiManNo).value = listBuManYiZhi[inset_bu_men]  # 直派-不满意
    if listJianMianZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhiJian).value = listJianMianZhi[inset_bu_men]  # 直派-见面
    if listWeiJianMianZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhiJianNo).value = listWeiJianMianZhi[inset_bu_men]  # 直派-未见面
    if lvLianXiZhi[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colZhilvLian).value = lvLianXiZhi[inset_bu_men]  # 直派-响应率
    if lvJieJueZhi[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colZhiLvJie).value = lvJieJueZhi[inset_bu_men]  # 直派-解决率
    if lvManYiFinalZhi[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colZhiLvMan).value = lvManYiFinalZhi[inset_bu_men]  # 直派-满意率
    if lvJianMianZhi[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colZhilvJian).value = lvJianMianZhi[inset_bu_men]  # 直派-见面率
    if lvLianXiAverage[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colAvgLian).value = lvLianXiAverage[inset_bu_men]  # 平均响应率
    if lvJieJueAverage[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colAvgJie).value = lvJieJueAverage[inset_bu_men]  # 平均解决率
    if lvManYiFinalAverage[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colAvgMan).value = lvManYiFinalAverage[inset_bu_men]  # 平均满意率
    if lvJianMianAverage[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colAvgJian).value = lvJianMianAverage[inset_bu_men]  # 平均见面率
    sheet_insert.cell(rowInsert, colDeFen).value = de_fen  # 得分
    if deFenLuoFen[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colDeFenLuoFen).value = deFenLuoFen[inset_bu_men]  # 裸分得分
    if shiFenLuoFen[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colShiFenLuoFen).value = shiFenLuoFen[inset_bu_men]  # 裸分失分
    # if is_she_qu == 1:
    #     sheet_insert.cell(rowInsert, colSuoShuPianQu).value = suoShuPianQu[inset_bu_men]  # 所属片区


def make_pai_ming(sheet_make, list_make, is_she_qu=0):
    tinyDictDeFen = {}
    tinyDictDeFenNone = {}
    for nameIndex in list_make:
        # nameIndex = list_make[indexName]
        lvManYiAll = None
        if listKaoHeZhi[nameIndex] + listKaoHeZhuan[nameIndex] > 0:
            if lvManYiAverage[nameIndex] is not None and lvJiBenAverage[nameIndex] is not None:
                lvManYiAll = lvManYiAverage[nameIndex]+lvJiBenAverage[nameIndex]*0.8
            else:
                if lvManYiAverage[nameIndex] is not None:
                    lvManYiAll = lvManYiAverage[nameIndex]
                if lvJiBenAverage[nameIndex] is not None:
                    lvManYiAll = lvJiBenAverage[nameIndex]*0.8
            numDeFen = round(lvLianXiAverage[nameIndex] * 0.1 + lvJieJueAverage[nameIndex] * 0.5 + lvManYiAll * 0.4)
            tinyDictDeFen[nameIndex] = numDeFen
        else:
            tinyDictDeFenNone[nameIndex] = listJieDanZhi[nameIndex] + listJieDanZhuan[nameIndex]
        # logger.debug('{} 得分：{} '.format(nameIndex, numDeFen))
    tinyDictDeFen = sorted(tinyDictDeFen.items(), key=lambda kv: kv[1], reverse=True)
    inertRow = 4
    for key in tinyDictDeFen:
        logger.debug('tinyDictDeFen {}行  承办部门：{} 得分：{}'.format(inertRow, key[0], key[1]))
        insert_into_sheet(sheet_make, inertRow, key[0], key[1], is_she_qu)
        inertRow = inertRow + 1
    tinyDictDeFenNone = sorted(tinyDictDeFenNone.items(), key=lambda kv: kv[1], reverse=True)
    for key in tinyDictDeFenNone:
        logger.debug('tinyDictDeFenNone {}行  承办部门：{}'.format(inertRow, key[0]))
        insert_into_sheet(sheet_make, inertRow, key[0], None, is_she_qu)
        inertRow = inertRow + 1

make_pai_ming(sheetKeShiResult, listKeShi)
make_pai_ming(sheetSheQuResult, listSheQu, 1)

# strMsgCount = strMain
# strMainKeshi = '备注：\r\n1.未提交办结工单的科室未在上表内列出\r\n2.' + strMain
# sheetKeShiResult.cell(sheetKeShiResult.max_row, 1).value = strMainKeshi
# strMainSheQu = '备注：\r\n1.未提交办结工单的社区（村）未在上表内列出\r\n2.标红的为解决率小于65%且见面率小于30%的社区（村）。'
# sheetSheQuResult.cell(sheetSheQuResult.max_row, 1).value = strMainSheQu

bookResultMuBan.save(fileResultSave)

# {回访工单办理情况}

strHuiFangInFo = '其中'
if countShuangMan > 0:
    strHuiFangInFo = strHuiFangInFo + '双满工单{}件，'.format(countShuangMan)
if countJieJueJiBenManYi > 0:
    strHuiFangInFo = strHuiFangInFo + '解决基本满意工单{}件，'.format(countJieJueJiBenManYi)
if countJieJueBuManYi > 0:
    strHuiFangInFo = strHuiFangInFo + '解决不满意工单{}件，'.format(countJieJueBuManYi)
if countWeiJieJue > 0:
    strHuiFangInFo = strHuiFangInFo + '未解决工单{}件，'.format(countWeiJieJue)
strHuiFangInFo = strHuiFangInFo + '共计失分工单{}件'.format(countJieJueJiBenManYi + countJieJueBuManYi + countWeiJieJue)

# 修改 每日必办 数据
for para in docMuBan.paragraphs:
    for run in para.runs:
        logger.debug('{}'.format(run.text))
        if '{明日日期}' in run.text:
            run.text = run.text.replace('{明日日期}', strTomorrow)
        if '考核期开始日期' in run.text:
            run.text = run.text.replace('{考核期开始日期}', strKaoHeStart)
        if '{今日截至时间}' in run.text:
            run.text = run.text.replace('{今日截至时间}', '{}月{}日12时'.format(timeCur.month, timeCur.day))
        if '{考核期接单总量}' in run.text:
            run.text = run.text.replace('{考核期接单总量}', '{}'.format(countInfo.jieDanCount))
        if '{接单总量区派}' in run.text:
            run.text = run.text.replace('{接单总量区派}', '{}'.format(countInfo.jieDanQuPai))
        if '{接单总量市派}' in run.text:
            run.text = run.text.replace('{接单总量市派}', '{}'.format(countInfo.jieDanShiPai))
        if '{考核期考核总量}' in run.text:
            run.text = run.text.replace('{考核期考核总量}', '{}'.format(countInfo.kaoHeCount))
        if countInfo.deFen != '-':
            if '{考核总量区派}' in run.text:
                run.text = run.text.replace('{考核总量区派}', '{}'.format(countInfo.kaoHeQuPai))
            if '{考核总量市派}' in run.text:
                run.text = run.text.replace('{考核总量市派}', '{}'.format(countInfo.kaoHeShiPai))
            if '{联系}' in run.text:
                run.text = run.text.replace('{联系}', '{}'.format(countInfo.lianXi))
            if '{未联系}' in run.text:
                run.text = run.text.replace('{未联系}', '{}'.format(countInfo.weiLanXi))
            if '{联系率}' in run.text:
                run.text = run.text.replace('{联系率}', '{}'.format(int(countInfo.lvLianXi)))
            if '{解决}' in run.text:
                run.text = run.text.replace('{解决}', '{}'.format(countInfo.jieJue))
            if '{未解决}' in run.text:
                run.text = run.text.replace('{未解决}', '{}'.format(countInfo.weiJieJue))
            if '{解决率}' in run.text:
                run.text = run.text.replace('{解决率}', '{}'.format(int(countInfo.lvJieJue)))
            if '{满意}' in run.text:
                run.text = run.text.replace('{满意}', '{}'.format(countInfo.manYi))
            if '{基本满意}' in run.text:
                run.text = run.text.replace('{基本满意}', '{}'.format(countInfo.jiBenManYi))
            if '{不满意}' in run.text:
                run.text = run.text.replace('{不满意}', '{}'.format(countInfo.buManYi))
            if '{满意率}' in run.text:
                run.text = run.text.replace('{满意率}', '{}'.format(int(countInfo.lvManYi)))
            if '{见面}' in run.text:
                run.text = run.text.replace('{见面}', '{}'.format(countInfo.jianMian))
            if '{未见面}' in run.text:
                run.text = run.text.replace('{未见面}', '{}'.format(countInfo.weiJianMian))
            if '{见面率}' in run.text:
                run.text = run.text.replace('{见面率}', '{}'.format(int(countInfo.lvJianMian)))
            if '{得分}' in run.text:
                run.text = run.text.replace('{得分}', '{}'.format(int(countInfo.deFen)))
            if '{区排名}' in run.text:
                run.text = run.text.replace('{区排名}', '{}'.format(int(countInfo.quPaiMing)))
        if '回访开始日期' in run.text:
            run.text = run.text.replace('{回访开始日期}', strHuiFangStart)
        if '{回访截止日期}' in run.text:
            run.text = run.text.replace('{回访截止日期}', '{}月{}日9时'.format(timeCur.month, timeCur.day))
        if '{办结工单总量}' in run.text:
            run.text = run.text.replace('{办结工单总量}', '{}'.format(countJieDanZhi+countJieDanZhuan))
        if '{办结总量区派}' in run.text:
            run.text = run.text.replace('{办结总量区派}', '{}'.format(countJieDanZhuan))
        if '{办结总量市派}' in run.text:
            run.text = run.text.replace('{办结总量市派}', '{}'.format(countJieDanZhi))
        if '{办结考核总量}' in run.text:
            run.text = run.text.replace('{办结考核总量}', '{}'.format(countKaoHeZhi+countKaoHeZhuan))
        if '{办结考核区派}' in run.text:
            run.text = run.text.replace('{办结考核区派}', '{}'.format(countKaoHeZhuan))
        if '{办结考核市派}' in run.text:
            run.text = run.text.replace('{办结考核市派}', '{}'.format(countKaoHeZhi))
        if '{办结联系}' in run.text:
            run.text = run.text.replace('{办结联系}', '{}'.format(countLianXiZhi + countLianXiZhuan))
        if '{办结未联系}' in run.text:
            run.text = run.text.replace('{办结未联系}', '{}'.format(countWeiLianXiZhi + countWeiLianXiZhuan))
        if '{办结联系率}' in run.text:
            run.text = run.text.replace('{办结联系率}', '{}'.format(int(round(lvLianxiCount))))
        if '{办结解决}' in run.text:
            run.text = run.text.replace('{办结解决}', '{}'.format(countJieJueZhi + countJieJueZhuan))
        if '{办结未解决}' in run.text:
            run.text = run.text.replace('{办结未解决}', '{}'.format(countWeiJieJueZhi + countWeiJieJueZhuan))
        if '{办结解决率}' in run.text:
            run.text = run.text.replace('{办结解决率}', '{}'.format(int(lvJieJueCount)))
        if '{办结满意}' in run.text:
            run.text = run.text.replace('{办结满意}', '{}'.format(countManYiZhi + countManYiZhuan))
        if '{办结基本满意}' in run.text:
            run.text = run.text.replace('{办结基本满意}', '{}'.format(countJiBenZhi + countJIBenZhuan))
        if '{办结不满意}' in run.text:
            run.text = run.text.replace('{办结不满意}', '{}'.format(countBuManYiZhi + countBuManYiZhuan))
        if '{办结满意率}' in run.text:
            run.text = run.text.replace('{办结满意率}', '{}'.format(int(round(lvManYiFinalCount))))
        if '{办结见面}' in run.text:
            run.text = run.text.replace('{办结见面}', '{}'.format(countJianMianZhi + countJianMianZhuan))
        if '{办结未见面}' in run.text:
            run.text = run.text.replace('{办结未见面}', '{}'.format(countWeiJianMianZhi + countWeiJianMianZhuan))
        if '{办结见面率}' in run.text:
            run.text = run.text.replace('{办结见面率}', '{}'.format(int(lvJianMianCount)))
        if '{回访工单办理情况}' in run.text:
            run.text = run.text.replace('{回访工单办理情况}', '{}'.format(strHuiFangInFo))
        if '{接单开始日期}' in run.text:
            run.text = run.text.replace('{接单开始日期}', '{}月{}日'.format(datatimeYesterday.month, datatimeYesterday.day))
        if '{接单结束日期}' in run.text:
            run.text = run.text.replace('{接单结束日期}', '{}月{}日'.format(timeCur.month, timeCur.day))
        if '{今日接单量}' in run.text:
            run.text = run.text.replace('{今日接单量}', '{}'.format(jieDanTodayInfo.jieDanCount))
        if '{今日区派}' in run.text:
            run.text = run.text.replace('{今日区派}', '{}'.format(jieDanTodayInfo.jieDanQuPai))
        if '{今日市派}' in run.text:
            run.text = run.text.replace('{今日市派}', '{}'.format(jieDanTodayInfo.jieDanShiPai))
        if '{前一日接单量}' in run.text:
            run.text = run.text.replace('{前一日接单量}', '{}'.format(jieDanTodayInfo.jieDanYesterday))
        if '{接单变化量}' in run.text:
            strReplace = ''
            if jieDanTodayInfo.jieDanCount > jieDanTodayInfo.jieDanYesterday:
                strReplace = '增加{}'.format(abs(jieDanTodayInfo.jieDanCount - jieDanTodayInfo.jieDanYesterday))
            else:
                strReplace = '减少{}'.format(abs(jieDanTodayInfo.jieDanCount - jieDanTodayInfo.jieDanYesterday))
            run.text = run.text.replace('{接单变化量}', strReplace)
        if '{环比结果}' in run.text:
            numChaZhi = jieDanTodayInfo.jieDanCount - jieDanTodayInfo.jieDanYesterday
            strReplace = ''
            if jieDanTodayInfo.jieDanYesterday != 0:
                if numChaZhi > 0:
                    strReplace = '上升{}'.format(abs(round(numChaZhi*100/jieDanTodayInfo.jieDanYesterday, 1)))
                else:
                    strReplace = '下降{}'.format(abs(round(numChaZhi*100/jieDanTodayInfo.jieDanYesterday, 1)))
            run.text = run.text.replace('{环比结果}', strReplace)
        if '{科室承办}' in run.text:
            run.text = run.text.replace('{科室承办}', '{}'.format(jieDanTodayInfo.jieDanKeShi))
        if '{社区村承办}' in run.text:
            run.text = run.text.replace('{社区村承办}', '{}'.format(jieDanTodayInfo.jieDanSheQu))
        if '{前几类}' in run.text:
            run.text = run.text.replace('{前几类}', '前{}类'.format(countMaxFenLeiStr[len(FenLeiCount) - 1]))
        if '{前几类接单量}' in run.text:
            run.text = run.text.replace('{前几类接单量}', '{}'.format(countMaxFenLei))
        if '{前几类占比}' in run.text:
            if jieDanTodayInfo.jieDanCount != 0:
                run.text = run.text.replace('{前几类占比}', '{}'.format(round(countMaxFenLei*100/jieDanTodayInfo.jieDanCount, 1)))
            else:
                run.text = run.text.replace('{前几类占比}', '0')

paiMingList = ['一', '二', '三', '四', '五', '六', '七', '八', '九', '十']
for table in docMuBan.tables:
    for row in table.rows:
        for cell in row.cells:
            logger.debug('{}'.format(cell.text))
            for paragraph in cell.paragraphs:
                for run in paragraph.runs:
                    logger.debug('{}'.format(run.text))
                    for paiMingIndex in paiMingList:
                        # 替换接单前五科室信息
                        if paiMingList.index(paiMingIndex) >= len(ChengBanCount):
                            break
                        logger.debug('{} {}'.format(paiMingIndex, paiMingList))
                        strChengBan = ChengBanCount[paiMingList.index(paiMingIndex)]
                        strReplace = '{接单第' + '{}'.format(paiMingIndex) + '名}'
                        if strReplace in run.text:
                            run.text = run.text.replace(strReplace, '{}'.format(strChengBan))
                        strReplace = '{第' + '{}'.format(paiMingIndex) + '名件数}'
                        if strReplace in run.text:
                            run.text = run.text.replace(strReplace, '{}'.format(countChengBan[strChengBan]))
                        strReplace = '{第' + '{}'.format(paiMingIndex) + '名占比}'
                        if strReplace in run.text:
                            strValue = round(countChengBan[strChengBan]*100/jieDanTodayInfo.jieDanCount, 1)
                            run.text = run.text.replace(strReplace, '{}'.format(strValue))
                        strReplace = '{第' + '{}'.format(paiMingIndex) + '名环比}'
                        if strReplace in run.text:
                            strValue = '-'
                            if strChengBan in ChengBanCountYesterday:
                                if ChengBanCountYesterday[strChengBan] != 0:
                                    valueLast = ChengBanCountYesterday[strChengBan]
                                    valueZeng = countChengBan[strChengBan] - ChengBanCountYesterday[strChengBan]
                                    if valueZeng > 0:
                                        strValue = '{}↑'.format(abs(valueZeng))
                                        run.font.color.rgb = RGBColor(255, 0, 0)
                                    if valueZeng < 0:
                                        strValue = '{}↓'.format(abs(valueZeng))
                                        run.font.color.rgb = RGBColor(0, 176, 80)
                                    if valueZeng == 0:
                                        strValue = '-'
                                else:
                                    strValue = '{}↑'.format(abs(countChengBan[strChengBan]))
                                    run.font.color.rgb = RGBColor(255, 0, 0)
                            run.text = run.text.replace(strReplace, strValue)


docMuBan.save(fileMustDoSave)
bookRiHuiZong.save(fileRiHuiZong)

# 准备 图1. 七日诉求量情况 数据
# 统计 七日诉求量 开始日期
dateCountList = {}
tuOneLabels = []
for dayJianGe in range(6, -1, -1):
    timeSearchDay = datetime.datetime.fromtimestamp(timeCur.timestamp() - 60 * 60 * 24 * dayJianGe).strftime('%Y-%m-%d')
    timeSearchDayStartStr = timeSearchDay + '  00:00:00'
    timeSearchDayEndStr = timeSearchDay + '  23:59:59'
    timeSearchDayStart = time.strptime(timeSearchDayStartStr, '%Y-%m-%d %H:%M:%S')
    timeSearchDayEnd = time.strptime(timeSearchDayEndStr, '%Y-%m-%d %H:%M:%S')
    dayCount = '{}月{}日'.format(timeSearchDayStart.tm_mon, timeSearchDayStart.tm_mday)
    logger.debug('{} 间隔 {} 天'.format(dayCount, dayJianGe))
    tuOneLabels.append(dayCount)
    dateCount = dateJieDanInFo()
    dateCountList[dayCount] = dateCount
logger.debug('{}'.format(tuOneLabels))

maxRow = sheetCountRiSuQiu.max_row
colDate = hanchxl.get_col_index(sheetCountRiSuQiu, '日期')
colRiSuQiu = hanchxl.get_col_index(sheetCountRiSuQiu, '日诉求量')
colRiQuPai = hanchxl.get_col_index(sheetCountRiSuQiu, '区派')
colRiShiPai = hanchxl.get_col_index(sheetCountRiSuQiu, '市派')
for row in range(2, maxRow + 1):
    valueDate = '{}'.format(sheetCountRiSuQiu.cell(row, colDate).value).strip()
    if valueDate in tuOneLabels:
        if sheetCountRiSuQiu.cell(row, colRiSuQiu).value is not None:
            dateCountList[valueDate].count = sheetCountRiSuQiu.cell(row, colRiSuQiu).value
        else:
            dateCountList[valueDate].count = 0
        if sheetCountRiSuQiu.cell(row, colRiQuPai).value is not None:
            dateCountList[valueDate].quPai = sheetCountRiSuQiu.cell(row, colRiQuPai).value
        else:
            dateCountList[valueDate].quPai = 0
        if sheetCountRiSuQiu.cell(row, colRiShiPai).value is not None:
            dateCountList[valueDate].shiPai = sheetCountRiSuQiu.cell(row, colRiShiPai).value
        else:
            dateCountList[valueDate].shiPai = 0

tuOneRiSuQiu = []
tuOneQuPai = []
tuOneShiPai = []
for index in tuOneLabels:
    tuOneRiSuQiu.append(dateCountList[index].count)
    tuOneQuPai.append(dateCountList[index].quPai)
    tuOneShiPai.append(dateCountList[index].shiPai)

logger.debug('tuOneLabels:{}'.format(tuOneLabels))
logger.debug('tuOneRiSuQiu：{}'.format(tuOneRiSuQiu))
logger.debug('tuOneQuPai：{}'.format(tuOneQuPai))
logger.debug('tuOneShiPai：{}'.format(tuOneShiPai))
jsPath = dirPath + '\\pyecharts_assets\\'
GenerateCharts.do_zu_he_tu_image(tuOneLabels, tuOneQuPai, '区派', tuOneShiPai, '市派', tuOneRiSuQiu, '日诉求量',
                                fileImageTuOneSave, jsPath, '图1. 七日诉求量情况')
# GenerateCharts.do_zu_he_tu_image(tuOneLabels, tuOneQuPai, '区派', tuOneShiPai, '市派', tuOneRiSuQiu, '日诉求量',
#                                 '图1. 七日诉求量情况.png', '图1. 七日诉求量情况')

FenLeiCountYesterday = []
maxRow = sheetCountFenLei.max_row
colDate = hanchxl.get_col_index(sheetCountFenLei, '日期')
for row in range(2, maxRow + 1):
    valueDate = '{}'.format(sheetCountFenLei.cell(row, colDate).value).strip()
    if valueDate == yesterdayStr:
        for index in FenLeiCount:
            colFenLei = hanchxl.get_col_index(sheetCountFenLei, index)
            if colFenLei is not None:
                valueFenLei = sheetCountFenLei.cell(row, colFenLei).value
                if valueFenLei is not None:
                    FenLeiCountYesterday.append(valueFenLei)
                else:
                    FenLeiCountYesterday.append(0)
            else:
                FenLeiCountYesterday.append(0)
        break

logger.debug('FenLeiCount：{}'.format(FenLeiCount))
logger.debug('FenLeiCountToday：{}'.format(FenLeiCountToday))
logger.debug('FenLeiCountYesterday：{}'.format(FenLeiCountYesterday))

strTuTwoTitle = '图2. 诉求集中反映前{}类问题'.format(countMaxFenLeiStr[len(FenLeiCount) - 1])
fileImageTuTwoSave = dirPath + '\\{}.png'.format(strTuTwoTitle)
GenerateCharts.do_zhu_tu_image(FenLeiCount, FenLeiCountToday, '今日诉求量', FenLeiCountYesterday, '前一日诉求量',
                               fileImageTuTwoSave, jsPath, strTuTwoTitle)

# para = docMuBan.paragraphs[0]
# para.clear()
# pic = para.add_run().add_picture(fileImageTuOneSave)
# pic.height = Cm(17.5)
# pic.width = Cm(35)

# fileMustDoSave1 = dirPath + '\\[内部] 每日必办 {}.{}-1.docx'.format(timeCur.month, timeCur.day)
# docMuBan.save(fileMustDoSave1)


